Rows to Columns - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Rows to Columns

Author

Topic

Dannyj
Starting Member

2 Posts

Posted - 2012-09-21 : 06:11:10

Hi all,

Hope you can help, i need to display the following table

select ID, Sort_Order, Code, start_date, end_date
from table1

ID, Sort_Order, Code, start_date, end_date
1, 1, A555, 01/01/2012, 05/01/2012
1, 2, B666, 01/01/2012, 05/01/2012
2, 1, C777, 08/01/2012, 10/01/2012
2, 2, D111, 08/01/2012, 10/01/2012
3, 1, E222, 15/01/2012, 20/01/2012
3, 2, F333, 15/01/2012, 20/01/2012

Like this;

ID, Code 1, Code 2, start_date, end_date,
1, A555, B666, 01/01/2012, 05/01/2012,
2, C777, D111, 08/01/2012, 10/01/2012,
3, E222, F333, 15/01/2012, 20/01/2012,

The sort_order can range between 1 - 100

Thanks for any help

Dany

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-21 : 06:32:13

with S
as (

select 1 as ID ,1 as SortOrder,'A555' as Code,'01/01/2012' as Start_date,'05/01/2012' as end_date
union all
select 1, 2, 'B666', '01/01/2012', '05/01/2012'
union all
select 2, 1, 'C777', '08/01/2012', '10/01/2012'
union all
select 2, 2, 'D111', '08/01/2012', '10/01/2012'
union all
select 3, 1, 'E222', '15/01/2012', '20/01/2012'
union all
select 3, 2, 'F333', '15/01/2012', '20/01/2012')

select * from S

pivot
(
min(Code) for SortOrder in ([1],[2])
) p

for 1--100 change in ([1],[2],...[100])

Dannyj
Starting Member

2 Posts

Posted - 2012-09-21 : 08:24:33

Thanks very much for your help, thats exactly what i needed.

Danny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources