Monday, February 20, 2012

pivot information

Hi there,
I haven't used queries to put data in columns in quite some time, so I'm
slightly rusty... I would appreciate some help with the following please:
(SQL 2000)
Query:
select [Date], MachNo,
case when Shift = 'A' then sum(Quantity) else 0 end as ShiftAQty,
case when Shift = 'B' then sum(Quantity) else 0 end as ShiftBQty,
case when Shift = 'C' then sum(Quantity) else 0 end as ShiftCQty
from Prod_Knitting_Data_1
group by [Date], MachNo, Shift
Result (extract):
Date Mc A B C
2008-01-07 00:00:00.000 01 0 0 400
2008-01-07 00:00:00.000 01 0 200 0
2008-01-07 00:00:00.000 02 0 0 0
2008-01-07 00:00:00.000 02 0 120 0
2008-01-07 00:00:00.000 03 0 0 180
2008-01-07 00:00:00.000 03 0 180 0
2008-01-07 00:00:00.000 03 60 0 0
As can be seen I need the results to be in one row for each date, machine
I know I'm missing something... but cannot recall what...
Thank you in advance!Change the query to take the SUM out of the CASE function:
SELECT [Date], MachNo,
SUM(CASE WHEN Shift = 'A' THEN Quantity ELSE 0 END) AS
ShiftAQty,
SUM(CASE WHEN Shift = 'B' THEN Quantity ELSE 0 END) AS
ShiftBQty,
SUM(CASE WHEN Shift = 'C' THEN Quantity ELSE 0 END) AS ShiftCQty
FROM Prod_Knitting_Data_1
GROUP BY [Date], MachNo
HTH,
Plamen Ratchev
E-Mail: Plamen@.Ratchev.com|||select [Date], MachNo,
sum(case when Shift = 'A' then Quantity else 0 end) as
ShiftAQty,
sum(case when Shift = 'B' then Quantity else 0 end) as
ShiftBQty,
sum(case when Shift = 'C' then Quantity else 0 end) as
ShiftCQty,
from Prod_Knitting_Data_1
group by [Date], MachNo
"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:21D07D5F-6113-403F-8E6D-FBB4F433DEFB@.microsoft.com...
> Hi there,
> I haven't used queries to put data in columns in quite some time, so I'm
> slightly rusty... I would appreciate some help with the following please:
> (SQL 2000)
> Query:
> select [Date], MachNo,
> case when Shift = 'A' then sum(Quantity) else 0 end as
> ShiftAQty,
> case when Shift = 'B' then sum(Quantity) else 0 end as
> ShiftBQty,
> case when Shift = 'C' then sum(Quantity) else 0 end as
> ShiftCQty
> from Prod_Knitting_Data_1
> group by [Date], MachNo, Shift
>
> Result (extract):
> Date Mc A B C
> 2008-01-07 00:00:00.000 01 0 0 400
> 2008-01-07 00:00:00.000 01 0 200 0
> 2008-01-07 00:00:00.000 02 0 0 0
> 2008-01-07 00:00:00.000 02 0 120 0
> 2008-01-07 00:00:00.000 03 0 0 180
> 2008-01-07 00:00:00.000 03 0 180 0
> 2008-01-07 00:00:00.000 03 60 0 0
>
> As can be seen I need the results to be in one row for each date, machine
> I know I'm missing something... but cannot recall what...
> Thank you in advance!

No comments:

Post a Comment