need some help on a pivot

Question

need some help on a pivot

torpkev

SSCarpal Tunnel

Points: 4819
More actions
June 29, 2010 at 3:07 pm

#387240

Hi all,
I was wondering if someone could help me out on a pivot here, I've done it before but am totally drawing a blank
I have data like this:
ShipDate, Qty, Department
06/28/2010, 100, 'Sanding'
06/28/2010, 150, 'Waxing'
06/28/2010, 130, 'Finishing'
06/28/2010, 82, 'Sanding'
07/05/2010, 24, 'Sanding'
07/05/2010, 45, 'Waxing'
07/05/2010, 12, 'Finishing'
07/12/2010, 100, 'Sanding'
07/12/2010, 84, 'Waxing'
What I want to get to is something like:
Shipdate, Sanding, Waxing, Finishing
06/28/2010, 182, 150, 130
07/05/2010, 24, 45, 12
07/12/2010, 100, 84, 0
I think I can use a PIVOT, but I can not for the life of me get it working..
any help would be greatly appreciated.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's a code snippet that should get you started.

If you have variable Departments you might want to consider using the DynamicCrossTab approach. If you're not familiar with it you should start with the CrossTab article refernenced in my signature (modify it until you get the same result as with PIVOT) followed by reading the DynamicCrossTabArticle. At the end you'll have a really flexible and well performing solution.

Or, as a totally different approach, you could do the pivoting at the frontend side... 😉

DECLARE @tbl TABLE

(

ShipDate DATETIME, Qty INT,Department VARCHAR(30)

)

INSERT INTO @tbl

SELECT '06/28/2010', 100, 'Sanding' UNION ALL

SELECT '06/28/2010', 150, 'Waxing' UNION ALL

SELECT '06/28/2010', 130, 'Finishing' UNION ALL

SELECT '06/28/2010', 82, 'Sanding' UNION ALL

SELECT '07/05/2010', 24, 'Sanding' UNION ALL

SELECT '07/05/2010', 45, 'Waxing' UNION ALL

SELECT '07/05/2010', 12, 'Finishing' UNION ALL

SELECT '07/12/2010', 100, 'Sanding' UNION ALL

SELECT '07/12/2010', 84, 'Waxing'

SELECT shipdate,Sanding, Waxing, Finishing

FROM

(

SELECT shipdate, department, SUM(qty) AS total

FROM @tbl

GROUP BY shipdate, department

) p

PIVOT

(

SUM (total)

FOR department IN ( Sanding, Waxing, Finishing )

) AS pvt

ORDER BY shipdate

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 2

That worked perfectly, thanks so much..

I was pretty close in what I had but wasn't quite there.. and the link on dynamic cross tab sounds pretty interesting - i'll definitely be checking that out when i get done with some work..

Thanks again!

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 3

torpkev (6/29/2010)
That worked perfectly, thanks so much..
I was pretty close in what I had but wasn't quite there.. and the link on dynamic cross tab sounds pretty interesting - i'll definitely be checking that out when i get done with some work..
Thanks again!

Glad I could help!:-D

Especially if the links I pointed you at will add some benefit (even though those are just "borrowed" articles from Jeff Moden). So, all I could claim for is pointing you at a different (/ right?) direction. If it works, its all my donation. If it doesn't, it'll be all Jeffs fault. 😀 (not that I've seen it happen within the time I've been around here though... 😉 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

lmu92 (6/29/2010)
torpkev (6/29/2010)
That worked perfectly, thanks so much..
I was pretty close in what I had but wasn't quite there.. and the link on dynamic cross tab sounds pretty interesting - i'll definitely be checking that out when i get done with some work..
Thanks again!
Glad I could help!:-D
Especially if the links I pointed you at will add some benefit (even though those are just "borrowed" articles from Jeff Moden). So, all I could claim for is pointing you at a different (/ right?) direction. If it works, its all my donation. If it doesn't, it'll be all Jeffs fault. 😀 (not that I've seen it happen within the time I've been around here though... 😉 )

Sounds like you've got it all figured out. 😛 (Thanks for the compliment, Lutz)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)