March 17, 2009 at 1:44 pm
SKUNumber PO# QTY ExpectedDate
640050193V15388122009-03-13 00:00:00.000
640050193V15534702009-06-05 00:00:00.000
640050193V155391802009-07-01 00:00:00.000
I have above result set in a table I want to convert PO# and other rows into columns based on common SKU so my row get rid common and put PO# and other rows in Column
SKUNumber PO#1 PO#2 PO#3 QTY1 QTY2 QTy3 ExpectedDate1
640050193V15388 V15534 V15539 12 12 180 2009-03-13
try lot of different things like cross join and stuff but can't figure out any Ideas?????
Thanks
March 17, 2009 at 2:31 pm
that how the data is
SKUPO# QTYDate
1'640050193''X1234' 122'03/13/2009'
2'640050193''V3344' 244'02/11/2009'
3'640050193''A1233' 43'02/9/2009'
I want to cnver it inot column this way
SKU PO#1 PO#2 Po#3 QTY1 QTY2 QTY3
1 '640050193' 'X1234' 'V3344' 'A1233' '122' '244' '43'
March 17, 2009 at 3:42 pm
Azeem, may I ask why you want it in columns like that? This is just out of curiousity about your business requirements. Sometimes there is an easier way to satisfy them.
Next question. Is there an upper limit to how many purchase orders might be associated with a single stock keeping unit? To accomplish a variable number of columns will require creating and executing a dynamic SQL string. I can help you with that, but please think about just how wide the output result set is going to be with all of the PO, QTY, and Date columns side by side.
If this is for a report, the ease of reading will decrease as the number of columns increases. It may not be too bad for three purchase orders, but it will start going bad very quickly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:28 pm
Our business rule don't allow more than 3 purchase order with in the Date range I am using for this Query
The max number of Purchase order are three same with QTY and Date as they related to PO. so each PO has a QTY and date.
PO's do vary though, some time there is only one PO with in this date range some time there are
two or three but never more than three.
9 columns in total.
March 17, 2009 at 4:35 pm
Hi Bob,
I am creating a Report , which shows for every stock unit what are the sales , inventory and
how many open Purchaseorder(PO) are out there , so if one Stock unit has two PO's the want them in one row instead of two. I am joining stock unit table with PO table but getting three
rows because of the multiple PO's.
March 17, 2009 at 9:49 pm
Understood, Azeem. I just wanted to make sure we didn't unleash a beast that was going to return 300 columns on a single row. Dynamic SQL can handle this. I'll show you how tomorrow, unless somebody beats me to it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply