SQl query presented data in pivot way - edited

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

SQl query presented data in pivot way - edited

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-07-16 : 08:34:43

Hi guys

kind of a similar question to what i asked before, i have been assked to present data in a certain way,

i have extracted the data as below

Pr cost Type key G
CW 244 EL 1 Dj
CW 22 NL 2 Dj
IM 24 EL 3 Dj
IM 140 NL 4 Dj
CW 100 EL 5 DRk
CW 23 NL 6 DRk
IM 45 EL 7 DRk
IM 40 NL 8 DRk
CW 46 EL 9 DRS
CW 23 NL 10 DRS
IM 23 EL 11 DRS
IM 12 NL 12 DRS
CW 10 EL 13 DZA
CW 0 NL 14 DZA
IM 0 EL 15 DZA
IM 0 NL 16 DZA

however the manager wants me to present this as below

G key [CW EL] [CW NL] [Im El] [im NL]
Dj 1 244 22 25 40
DRk 2 100 23 45 40
DRS 3 46 23 23 12
DZA 4 10 0 0 0

can anyone help me scrip this?

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 09:43:30

[code]
SELECT G,
ROW_NUMBER() OVER (ORDER BY MIN([Key])) AS [Key],
SUM(CASE WHEN Pr='CW' AND Type='EL' THEN cost ELSE 0 END) AS [CW EL],
SUM(CASE WHEN Pr='CW' AND Type='NL' THEN cost ELSE 0 END) AS [CW NL],
SUM(CASE WHEN Pr='CW' AND Type='EL' THEN cost ELSE 0 END) AS [im EL],
SUM(CASE WHEN Pr='CW' AND Type='NL' THEN cost ELSE 0 END) AS [im NL]
FROM table
GROUP BY G
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources