Swivel Column

Question

I am trying to PIVOT some data in a table, but I cannot do this because I cannot find a way to do this using varchar columns. I have this table:

declare @table table(name VARCHAR(50) not null, occupation VARCHAR(MAX)) insert into @table values ('A','Doctor') insert into @table values ('B','Doctor') insert into @table values ('A','Professor') insert into @table values ('A','Singer') insert into @table values ('A','Actor') SELECT CASE WHEN occupation = 'Doctor' THEN NAME END AS Doctor, CASE WHEN occupation = 'Professor' THEN NAME END AS Professor, CASE WHEN occupation = 'Singer' THEN NAME END AS Singer, CASE WHEN occupation = 'Actor' THEN NAME END AS Actor FROM @table 

Output:

 Doctor Professor Singer Actor A NULL NULL NULL B NULL NULL NULL NULL A NULL NULL NULL NULL A NULL NULL NULL NULL A 

And for Pivot, I get this output:

 select * from ( select name, occupation from @table ) src pivot ( min(name) for occupation in ([Doctor],[Professor],[Singer],[Actor])) as pvt Doctor Professor Singer Actor AAAA 

And for the min / max / function function, the pivot function gives me only partial output, for the count function I get the number of records for the doctor, singer, etc. But I need real rows, not the number of rows.

I need this:

 Doctor Professor Singer Actor AAAAB NULL NULL NULL 

Suppose if we have 5 names for doctors, we need to show 5 entries for the doctor’s column.

Answer 1

I find this easier to express as conditional aggregation using the sequence number generated with `row_number ():

 select max(case when occupation = 'Doctor' then name end) as Doctor, max(case when occupation = 'Professor' then name end) as Professor, max(case when occupation = 'Singer' then name end) as Singer, max(case when occupation = 'Actor' then name end) as Actor from (select t.*, row_number() over (partition by occupation order by name) as seqnum from @table t ) t group by seqnum order by seqnum; 

Answer 2

You can use PIVOT as you suggested, just add a column with ROW_NUMBER :

 SELECT [Doctor],[Professor],[Singer],[Actor] FROM (SELECT name, occupation, rn = ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY occupation) FROM @table ) AS src PIVOT ( MIN(name) FOR occupation IN ([Doctor],[Professor],[Singer],[Actor]) ) AS pvt 

LiveDemo

Output:

 ╔════════╦═══════════╦════════╦═══════╗ ║ Doctor ║ Professor ║ Singer ║ Actor ║ ╠════════╬═══════════╬════════╬═══════╣ ║ A ║ A ║ A ║ A ║ ║ B ║ ║ ║ ║ ╚════════╩═══════════╩════════╩═══════╝ 

EDIT:

You did not write how to process more lines, therefore consider this case. Above solution will return:

 ╔════════╦═══════════╦════════╦═══════╗ ║ Doctor ║ Professor ║ Singer ║ Actor ║ ╠════════╬═══════════╬════════╬═══════╣ ║ A ║ A ║ A ║ A ║ ║ B ║ ║ C ║ ║ ╚════════╩═══════════╩════════╩═══════╝ 

vs

 ╔════════╦═══════════╦════════╦═══════╗ ║ Doctor ║ Professor ║ Singer ║ Actor ║ ╠════════╬═══════════╬════════╬═══════╣ ║ A ║ A ║ A ║ A ║ ║ B ║ ║ ║ ║ ║ ║ ║ C ║ ║ ╚════════╩═══════════╩════════╩═══════╝ 

If you want to use the second case:

 SELECT [Doctor],[Professor],[Singer],[Actor] FROM (SELECT name, occupation, rn = DENSE_RANK() OVER (ORDER BY Name) FROM @table ) AS src PIVOT ( MIN(name) FOR occupation IN ([Doctor],[Professor],[Singer],[Actor]) ) AS pvt 

LiveDemo2