Transpose Rows Into Column - 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 2000 Forums

SQL Server Development (2000)

Transpose Rows Into Column

Author

Topic

mkhalid346
Starting Member

1 Post

Posted - 2011-12-27 : 11:51:54

I have data in my table as follows

ClientID Product Price
1 Head & Sholder 63
1 Dove 60
1 SunSilk 60
2 Head & Sholder 65
2 Dove 61
2 SunSilk 69
3 Head & Sholder 65
3 Dove 68
3 SunSilk 75
4 Head & Sholder 68
4 Dove 58
4 SunSilk 52

I want Result as Follow

Client Id Head & Sholder Dove SunSilk
1 63 60 60
2 65 61 69
3 65 68 75
4 68 58 52

Pleae Help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 12:07:40

If the list of Products is static, you can do a "manual pivoting" - sort of like this:

SELECT
   clientId,
   MAX(CASE WHEN product = 'Head & Sholder' THEN Price END) AS [Head & Sholder],
   MAX(CASE WHEN product = 'Dove' THEN Price END) AS Dove,
   MAX(CASE WHEN product = 'SunSilk' THEN Price END) AS SunSilk
FROM
   YourTable
GROUP BY
   clientId

If your Products are not known in advance, then you would need dynamic pivoting. Take a look at Madhivanan's blog here

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 12:46:53

if its dynamic use this

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources