Columns to Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Columns to Rows

Author

Topic

b8538230
Starting Member

16 Posts

Posted - 2012-02-21 : 20:50:51

HI -- I thought this problem of mine would be good to use PIVOT. Now, I'm not so sure. Here's the input table:


Agenda  Event  Time
------  -----  -----
1        A      9:00
1        B     10:00
1        C     11:00
1        D     12:00
1        A      1:00
1        B      2:00
1        C      3:00
.
.
.
2        A ...

...etc.

What I want to get:


Agenda  EventA  EventB  EventC
1       9:00    10:00   11:00
1       1:00     2:00    3:00

...

In any agenda, Event A must precede Event B which must precede Event C. However, I can have Event A without B and C and I can have events A and B without event C.

What succinct query that will do this for me?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-21 : 20:56:16

Yes you can use PIVOT


select *
from   yourtable t
       pivot
       (
             max(Time)
             for Event in ([A], [B], [C], [D])
       ) p

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 00:15:57

[code]
SELECT Agenda,[A], [B], [C], [D]
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Agenda,Event ORDER BY Event) AS Rn
FROM table
)t
PIVOT (max(Time) for Event in ([A], [B], [C], [D])
)p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

b8538230
Starting Member

16 Posts

Posted - 2012-02-22 : 09:05:11

quote:

Originally posted by khtan

Yes you can use PIVOT


select *
from   yourtable t
       pivot
       (
             max(Time)
             for Event in ([A], [B], [C], [D])
       ) p

Hmmm... This doesn't give me what I want. Consider this example:


with mytable(n,e,t)  as (
	select * from (values
		(1, 'A', '9:00'),
		(1, 'B', '10:00'),
		(1, 'C', '11:00'),
		(1, 'D', '12:00'),
		(1, 'A', '13:00'),
		(1, 'B', '14:00'),
		(1, 'C', '15:00')
		) a(b,c,d)
	)
	
    select * from mytable me
       pivot
       (
             max(t)
             for e in ([A], [B], [C], [D])
       ) p

This produces only one row of output:


n	A	B	C	D
1	9:00	14:00	15:00	12:00

I want to see:


n	A	B	C	D
1	9:00	10:00	11:00	12:00
1      13:00    14:00   15:00   NULL

b8538230
Starting Member

16 Posts

Posted - 2012-02-22 : 09:14:10

quote:

Originally posted by visakh16


SELECT Agenda,[A], [B], [C], [D]
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Agenda,Event ORDER BY Event) AS Rn
FROM table
)t
PIVOT (max(Time) for Event in ([A], [B], [C], [D])
)p

I'm afraid that this doesn't work. I get errors:


with mytable(n,e,t)  as (
	select * from (values
		(1, 'A', '9:00'),
		(1, 'B', '10:00'),
		(1, 'C', '11:00'),
		(1, 'D', '12:00'),
		(1, 'A', '13:00'),
		(1, 'B', '14:00'),
		(1, 'C', '15:00')
		) a(b,c,d)
	)
	
	select n, [A],[B],[C],[D]
	from (
		select *, ROW_NUMBER() over (partition by n, e order by e) as rn
		from   mytable
		   pivot
		   (
				 max(t)
				 for e in ([A], [B], [C], [D])
		   ) p
	) foo

Gives these errors:


Msg 207, Level 16, State 1, Line 15
Invalid column name 'e'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'e'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-22 : 09:16:13

[code]
select n, A, B, C, D
from (
select *, r = row_number() over (partition by e order by t)
from mytable me
) me
pivot
(
max(t)
for e in ([A], [B], [C], [D])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

b8538230
Starting Member

16 Posts

Posted - 2012-02-22 : 09:28:57

quote:

Originally posted by khtan


    	select 	n, A, B, C, D
	from 	(
			select	*, r = row_number() over (partition by e order by t)
			from	mytable me
		) me
	       	pivot
	       	(
	             max(t)
	             for e in ([A], [B], [C], [D])
	       	) p

KH
[spoiler]Time is always against us[/spoiler]

Worked!

Thanks a zillion!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources