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

Bhuvans gravatar image

Bhuvans
220 19 19 21

(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

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1951
x1850
x82

asked: Mar 13, 2012 at 12:49 PM

Seen: 785 times

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