Pivot Column of varchar

Question

I´m trying to PIVOT some data in a table, but I cannot do it because I do not find the way to do it 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
A        A          A       A   

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

What I need is this:

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

i.e suppose if we have 5 name for doctors we need to show 5 entries for doctor column.

Answer 1

I find this easier to express as conditional aggregation using a sequential number generated using `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 proposed, just add 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 handle more rows so 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 second case use:

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