Transform a string result table from rows to columns

Question

How could I transform a table that looks like this:

colname	entity
topic01	T_pitch
topic01	T_someg
topic02	T_gold
topic02	sp_gpdf
topic02	T_someg
topic03	sp_gpdf2

to this

topic01	topic02	topic03
T_pitch	T_gold	sp_gpdf2
T_someg	sp_gpdf
	T_someg

dynamically.

I tried pivoting it, but PIVOT tables are designed to aggregate the results. Using

max(entity) for colname in ('+ @columns +') 

only results in one entity shown under each topic.

Answer 1

If you number the rows with ROW_NUMBER(), PIVOT is an option. The important part here is how do you expect to order the rows.

Sample data:

SELECT *
INTO Data
FROM (VALUES  
   ('topic01', 'T_pitch'),
   ('topic01', 'T_someg'),
   ('topic02', 'T_gold'),
   ('topic02', 'sp_gpdf'),
   ('topic02', 'T_someg'),
   ('topic03', 'sp_gpdf2')
) v (colname, entity)  

Statement for static PIVOT:

SELECT [topic01], [topic02], [topic03]
FROM (  
   SELECT colname, entity, ROW_NUMBER() OVER (PARTITION BY colname ORDER BY entity) AS rn
   FROM Data
) t
PIVOT (
   MAX(entity) FOR colname IN ([topic01], [topic02], [topic03])
) p 

Results:

topic01	topic02	topic03
T_pitch	sp_gpdf	sp_gpdf2
T_someg	T_gold	null
null	T_someg	null