Pivot Data - 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)

Pivot Data

Author

Topic

Syphor
Starting Member

2 Posts

Posted - 2009-03-15 : 18:27:20

Hello all,

I hope I'm in the right fourm here. I'm pretty new to using SQL and I've already run into a problem I can't solve. I've done some reading on pivot tables and think that may help me but I'm not sure how to make it work properly to get the result I need.

I have some data that is on seperate rows that I'm trying to change to report in colums. Here is an example of the table holding my data that I'm using. It is an order number with 2 lines of data with 3 groups with a value.

Order# | Line# | Group | Value
1 | 1 | Group1 | 5
1 | 1 | Group2 | 6
1 | 1 | Group3 | 2
1 | 2 | Group1 | 3
1 | 2 | Group2 | 7
1 | 2 | Group3 | 3

What I'm trying to achive is to change the data output to give me 2 lines of data like this:

Order# | Line# | Group1 | Group2 | Group3
1 | 1 | 5 | 6 | 2
1 | 2 | 3 | 7 | 3

Is this possible to do? and if so could anyone help me out? it would be much appreciated.

Thank you,
Andy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-15 : 21:12:17

[code]select
[Order#],
[Line#],
max(case when [Group] = 'Group1' then [Value] end) as Group1,
max(case when [Group] = 'Group2' then [Value] end) as Group2,
max(case when [Group] = 'Group2' then [Value] end) as Group3
from
theTable
group by
[Order#],
[Line#]
[/code]