x

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?

more ▼

asked Mar 13, 2012 at 12:49 PM in Default

avatar image

Bhuvans
220 19 19 24

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
 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
more ▼

answered Mar 13, 2012 at 02:41 PM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2077
x2018
x103

asked: Mar 13, 2012 at 12:49 PM

Seen: 931 times

Last Updated: Mar 13, 2012 at 02:54 PM

Copyright 2016 Redgate Software. Privacy Policy