pivot question

Hello,
How can I build a table that pivots Date and Qty, but uses Price from first
month?
Have this:
Part Price Date Qty
1A 5 1/05 10
1A 6 2/05 20
1A 7 3/05 30
1B 2 1/05 10
1B 1 2/05 30
1B 3 3/05 40
Want this: Spend = Price*Qty
Part Price Jqty Fqty Mqty JSpend FSpend MSpend
1A 5 10 20 30 50 100
150
1B 2 10 30 40 20 60
80
thx all, shHi
Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett__e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.__htm#inserts
It is also useful to post your current attempts at solving the problem.
Check out the links on http://tinyurl.com/6rhsj for crosstab solutions.
For example:
SELECT M.Part,
JanPrice = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
'20050101' AND Part = M.Part),0),
JanQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050101'
AND Part = M.Part),0),
FebQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050201'
AND Part = M.Part),0),
MarQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050301'
AND Part = M.Part),0),
JanSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
'20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES WHERE
[Date] = '20050101' AND Part = M.Part),0),
FebSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
'20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES WHERE
[Date] = '20050201' AND Part = M.Part),0),
MarSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
'20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES WHERE
[Date] = '20050301' AND Part = M.Part),0)
FROM MONTHSALES M
GROUP BY M.[Part]
ORDER BY M.[Part]
or:
SELECT M.[Part],
JanPrice = ISNULL(J.[Price],0),
JanQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050101'
AND Part = M.Part),0),
FebQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050201'
AND Part = M.Part),0),
MarQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] = '20050301'
AND Part = M.Part),0),
JanSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
WHERE [Date] = '20050101' AND Part = M.Part),0),
FebSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
WHERE [Date] = '20050201' AND Part = M.Part),0),
MarSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
WHERE [Date] = '20050301' AND Part = M.Part),0)
FROM MONTHSALES M
JOIN ( SELECT [Part], [Price] FROM MONTHSALES WHERE [Date] = '20050101' ) J
ON J.[Part] = M.[Part]
GROUP BY M.[Part], J.[Price]
ORDER BY M.[Part]
John
"Steve H" <steve9@.gmail.com> wrote in message
news:%23zAFhoYMFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I build a table that pivots Date and Qty, but uses Price from
> first month?
> Have this:
> Part Price Date Qty
> 1A 5 1/05 10
> 1A 6 2/05 20
> 1A 7 3/05 30
> 1B 2 1/05 10
> 1B 1 2/05 30
> 1B 3 3/05 40
> Want this: Spend = Price*Qty
> Part Price Jqty Fqty Mqty JSpend FSpend MSpend
> 1A 5 10 20 30 50 100 150
> 1B 2 10 30 40 20 60 80
> thx all, sh
>|||John, thx for the advice (and solution)...I like to keep it simple, to a
fault sometimes (as illustrated by the lack of response to this post?). I
ended up using a temp table to hold the price for the first month, got the
job done, but not efficient.
Steve H
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23Il$kRfMFHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi
> Also check out how to post DDL and example data at
> http://www.aspfaq.com/etiquett__e.asp?id=5006 and
> example data as insert statements
> http://vyaskn.tripod.com/code.__htm#inserts
> It is also useful to post your current attempts at solving the problem.
> Check out the links on http://tinyurl.com/6rhsj for crosstab solutions.
> For example:
> SELECT M.Part,
> JanPrice = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0),
> JanQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0),
> FebQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050201' AND Part = M.Part),0),
> MarQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050301' AND Part = M.Part),0),
> JanSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050101' AND Part = M.Part),0),
> FebSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050201' AND Part = M.Part),0),
> MarSpend = ISNULL((SELECT Price FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050301' AND Part = M.Part),0)
> FROM MONTHSALES M
> GROUP BY M.[Part]
> ORDER BY M.[Part]
> or:
> SELECT M.[Part],
> JanPrice = ISNULL(J.[Price],0),
> JanQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050101' AND Part = M.Part),0),
> FebQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050201' AND Part = M.Part),0),
> MarQty = ISNULL((SELECT QTY FROM MONTHSALES WHERE [Date] =
> '20050301' AND Part = M.Part),0),
> JanSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050101' AND Part = M.Part),0),
> FebSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050201' AND Part = M.Part),0),
> MarSpend = ISNULL(J.Price,0) * ISNULL((SELECT QTY FROM MONTHSALES
> WHERE [Date] = '20050301' AND Part = M.Part),0)
> FROM MONTHSALES M
> JOIN ( SELECT [Part], [Price] FROM MONTHSALES WHERE [Date] = '20050101' )
> J ON J.[Part] = M.[Part]
> GROUP BY M.[Part], J.[Price]
> ORDER BY M.[Part]
>
> John
>
>
> "Steve H" <steve9@.gmail.com> wrote in message
> news:%23zAFhoYMFHA.3340@.TK2MSFTNGP14.phx.gbl...
>

Pivot Key Values

Is it possible to use a expression as a pivot key value?

I need to take data from one table and pivot it into another but what pivots to where is conditional, so I'm wondering if it's possible to say something like

"where Column1 = 'A' and Column2 = 'B' " in the pivot key value.

I'm trying to get it to work but can't, just keep getting the 'No pivot Key found' error.

Thanks

bobbins,

How many conditions are there? You could just use a conditional split to grab the records with the correct values per type and use a seperate chain for each, doing a union all to get them back together after the chain.

--

Looking at it more closely you are probably trying to unpivot (un-normalize). You could possibly use an expression to do the trick. The expression is located on the data flow task and is referenced in a manner similar to Unpivot.Unpivot Input.ColumnName.PivotKeyValue . (NOTE: both pivot and unpivot expose the pivotkeyvalue as data flow property expressions) Although, to be perfectly honest, I'm not sure if you will have access to the record information going through the pipe at the moment or not, so that might not work either...

|||You could pivot the values with a script. Using the script allows you a lot more flexibility in defining the pivoting rules.|||

Thanks for the replies, I am trying to de-normalize the data, here is a better explanation of what I'm trying to do:

My source data is an Ingres db on Unix:

MemberID Date Value Code Type 5029348 01/12/2006 79 A 1 5029348 01/12/2006 8 B 2 5029348 01/12/2006 4 C 3

I want to put the data in a de-normalized table so it looks like this:

MemberID Date ValueA1 ValueB2 ValueC3 5029348 01/12/2006 79 8 4

So my data mapping rules are:

Where Code = A and Type = 1 then map to ValueA1

Where Code = B and Type = 2 then map to ValueB2

Where Code = C and Type = 3 then map to ValueC3

I would like to do this as the data comes through the pipe instead of creating a staging table at either the source end or destination and then just bumping the data straight in from the staging table. I have limited experience with SSIS and want to learn but I'm struggling to work out what to do in the time I've been given to do this, hence my question about what you can actually put in the Pivot Key Values. A conditional split will split the values out but how do I put them all back together again as one row per MemberID to go into the destination? Or is there another way to do this?

Thanks again

|||

Take a look at this post. http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

You'll have to alter the script to not use the Split function, and apply your mapping rules, but it should provide a good starting point. If you are still having problems, post back here and we'll help.