question

declare @Perspectives table (name varchar(10), data varchar(50)) insert into @Perspectives select 'AAA', 'Question1' insert into @Perspectives select 'BBB', 'Question2' insert into @Perspectives select 'AAA', 'Question3' insert into @Perspectives select 'AAA', 'Question4' insert into @Perspectives select 'BBB', 'Question5' select AAA, BBB from (select row_number() over( partition by name order by data) as rowid, name, data from @Perspectives)p pivot (max(data) for name in ([AAA],[BBB])) as pvt or if you need a more dynamic solution that doesn't require all the values of `name` to be known ahead of run-time (needs a table, rather than table variable) create table Perspectives (name varchar(10), data varchar(50)) insert into Perspectives select 'AAA', 'Question1' insert into Perspectives select 'BBB', 'Question2' insert into Perspectives select 'AAA', 'Question3' insert into Perspectives select 'AAA', 'Question4' insert into Perspectives select 'BBB', 'Question5' select AAA, BBB from (select row_number() over( partition by name order by data) as rowid, name, data from Perspectives)p pivot (max(data) for name in ([AAA],[BBB])) as pvt DECLARE @cols NVARCHAR(2000) SELECT @cols = COALESCE(@cols + ',[' + name + ']','[' + name + ']') FROM (select distinct Name from Perspectives) a DECLARE @query NVARCHAR(4000) SET @query = N'SELECT '+ @cols +' FROM (select row_number()over(partition by name order by (select null)) as rowid, name, data from Perspectives)p PIVOT ( MAX(data) FOR Name IN ( '+ @cols +' ) ) AS pvt;' execute (@query) drop table Perspectives