# question

## Need help in Pivoting the Data

Hi, I had table "Perspectives" like this > Name Data > AAA Question1 > BBB Question2 > AAA Question3 > AAA Question4 > BBB Question5 I need an output like this > AAA BBB > Question1 Question2 > Question3 Question5 > Question4 NULL So is it possible to do like this?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.