Simple Transpose - why can't I do it??

Question

Simple Transpose - why can't I do it??

david.kotchie

Old Hand

Points: 300
More actions
September 10, 2013 at 8:37 pm

#282337

Guri (is that the plural of Guru?)
I have a result set that I need to present in a graph:
Results:
CategoryPrevWk2PrevWk1CurrWk
BlueStore 1.5 3 8.25
RedStore 4.5 4.515.5
and I need it to be:
Week: BlueStore RedStore
PrevWk2 1.5 4.5
PrevWk1 3 4.5
CurrWk 8.25 15.5
for the SSRS Graph, so that the X axis will be the weeks and the series will be the Red & Blue values...
I have looked at the PIVOT and Case examples and I still can't get it to work.
Can anyone help a brother out?
(Feeling a bit daft!!)
Cheers,
David

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

I am not sure if this is the best way to do it. But, this is what I could come up with( An UNPIVOT followed by a CROSS-TAB )..

DECLARE@tbl TABLE

(

CategoryVARCHAR(10),

PrevWk2NUMERIC(5,2),

PrevWk1NUMERIC(5,2),

CurrWkNUMERIC(5,2)

)

INSERT@tbl

( Category, PrevWk2, PrevWk1, CurrWk )

SELECT'BlueStore', 1.5, 3, 8.25 UNION ALL

SELECT'RedStore', 4.5, 4.5, 15.5

SELECTUP.Wk,

SUM( CASE WHEN Category = 'BlueStore' THEN Amount ELSE 0 END ) AS BlueStore,

SUM( CASE WHEN Category = 'RedStore' THEN Amount ELSE 0 END ) AS RedStore

FROM@tbl AS T

UNPIVOT(

Amount FOR Wk IN ( PrevWk2, PrevWk1, CurrWk )

) AS UP

GROUP BY UP.Wk

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

david.kotchie Old Hand Points: 300 More actions · Answer 2

Kingston,

I feel like I owe you my firstborn!! Can't thank you enough for this, worked like a charm!!!

You guri know your stuff. I should have known it was already pivotted data, so an UNPIVOT was required.

Thanks again!!

Cheers,

David

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

david.kotchie (9/12/2013)
Kingston,
I feel like I owe you my firstborn!! Can't thank you enough for this, worked like a charm!!!
You guri know your stuff. I should have known it was already pivotted data, so an UNPIVOT was required.
Thanks again!!
Cheers,
David

Thanks for the feedback and I am glad I could help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/