Pivot; With clause; Aggregate?

Question

Pivot; With clause; Aggregate?

David Hart-382918

SSChasing Mays

Points: 657
More actions
April 29, 2008 at 3:06 pm

#185894

I have a result set from a script listed as result set#1 in my attachment. This collects orders by LotNumber.
From this result, I need to aggregate or pivot by LotNumber the QtyShipped, and most importantly, concatenate the attendant tracking numbers in one column as displayed in Result Set#2.
I am hopeful this is sufficient info to find a starting point.

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

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Most of this is straight forward... the comma separated list you want to make can be done by using the technique in the following...

http://www.sqlservercentral.com/articles/Test+Data/61572/

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

David Hart-382918 SSChasing Mays Points: 657 More actions · Answer 2

Thanks Jeff. The link really helped. I settled on the SQL2005 'stuff' function. Once I kicked it around enough times to get the joins right, it was perfect.

STUFF((SELECT ', '+ trk2.trackno

FROM tblTracking trk2

WHERE Trk2.OrderID=Trk.OrderID

AND Trk2.ProductNo=Trk.ProductNo

FOR XML PATH('')),1,1,'') ASTrackingNumbers,

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

If you're hell-bound on using this in a pivot, you might care to check out one of the SQL 2005 alternatives Jeff didn't include in the article: a user-defined aggregate function. If you're interested, you can steal the code right out of Books online, since their example of a UDA is a concatenation function....

http://msdn.microsoft.com/en-us/library/ms131056.aspx

It will only allow for varchar(8000) as the max return size, so it's certainly not perfect, but it does work pretty well.

Otherwise - you may need to somehow encapsulate the STUFF into an aggregate somehow (or use the "old-style" pivot syntax.)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?