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