Hey guys, hopefully you can help me with my request... I am pretty new to SQL, so please bear with me..
I have a table on the system as follows
Item | Year | Month | ucivqa01 |AVG PRICE
ABC 2007 1 10 2.00
ABC 2007 2 10 2.10
ABC 2007 3 10 2.05
ABC 2007 4 10 2.30
ABC 2007 5 10 5.00
ABC 2007 6 10 1.95
XYZ 2007 1 10 100
XYZ 2007 2 10 112
XYZ 2007 3 10 111
XYZ 2007 4 10 100
XYZ 2007 5 10 105
XYZ 2007 6 10 104
I am trying to return the data in the following format:
ITEM MNTH1 MNTH2 MNTH3 MNTH4 MNTH5 MNTH 6 AVG1 AVG2 AVG3 AVG4 AVG5 AVG6
ABC 10 10 10 10 10 10 2.00 2.10 2.05 2.30 5.00 1.95
XYZ 10 10 10 10 10 10 100 112 111 100 105 104
This is only a small sample of the data, and i will be using the resulting SQL over a larger number or records. Also, the month and year will change. . I have a requirement to populate this data based on 41 months prior to the current month,
Anyway, i would appricate any help.
thanks
scotty
Here you go...
Code Snippet
Create Table #data (
Item Varchar(100) ,
Year Varchar(100) ,
Month int ,
ucivqa01 int ,
AVGPRICE float
);
Insert Into #data Values('ABC','2007','1','10','2.00');
Insert Into #data Values('ABC','2007','2','10','2.10');
Insert Into #data Values('ABC','2007','3','10','2.05');
Insert Into #data Values('ABC','2007','4','10','2.30');
Insert Into #data Values('ABC','2007','5','10','5.00');
Insert Into #data Values('ABC','2007','6','10','1.95');
Insert Into #data Values('XYZ','2007','1','10','100');
Insert Into #data Values('XYZ','2007','2','10','112');
Insert Into #data Values('XYZ','2007','3','10','111');
Insert Into #data Values('XYZ','2007','4','10','100');
Insert Into #data Values('XYZ','2007','5','10','105');
Insert Into #data Values('XYZ','2007','6','10','104');
Select
Item
,Max(Case When Year=2007 And Month=1 Then ucivqa01 End) Month1
,Max(Case When Year=2007 And Month=2 Then ucivqa01 End) Month2
,Max(Case When Year=2007 And Month=3 Then ucivqa01 End) Month3
,Max(Case When Year=2007 And Month=4 Then ucivqa01 End) Month4
,Max(Case When Year=2007 And Month=5 Then ucivqa01 End) Month5
,Max(Case When Year=2007 And Month=6 Then ucivqa01 End) Month6
,Avg(Case When Year=2007 And Month=1 Then AVGPRICE End) Avg1
,Avg(Case When Year=2007 And Month=2 Then AVGPRICE End) Avg2
,Avg(Case When Year=2007 And Month=3 Then AVGPRICE End) Avg3
,Avg(Case When Year=2007 And Month=4 Then AVGPRICE End) Avg4
,Avg(Case When Year=2007 And Month=5 Then AVGPRICE End) Avg5
,Avg(Case When Year=2007 And Month=6 Then AVGPRICE End) Avg6
From
#data
Group By
Item
|||
Thanks Manivannan, that is a great help.
I also heard from someone else about using the pivot command instead. From that i was able to build the following up:
(column names are different as this is live data)
SELECT*
FROM(SELECT ITEM, CUSTOMER, InvMONTH, QTY, [Unit Price], PrcMonth
FROM mvxreport.dmsexportstage1)
SOURCEQUERY PIVOT(max(QTY)FOR [InvMONTH] IN([INV1], [INV2], [INV3], [INV4], [INV5], [INV6]))
AS PIVOTTABLE PIVOT(
max([Unit Price])FOR [PrcMONTH] IN([PRC1], [PRC2], [PRC3], [PRC4], [PRC5], [PRC6]))AS PIVOTTABLE1
But when i do this, i cannot group by Item, Customer.. I get the results below:
| 9002-6050 | AGCO | NULL | NULL | NULL | NULL | 7 | NULL | NULL | NULL | NULL | NULL | 6.27 | NULL |
| 9002-6050 | AIMS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 9002-6050 | AIMS | NULL | NULL | 4 | NULL | NULL | NULL | NULL | NULL | 6.87 | NULL | NULL | NULL |
| 9002-6050 | AIMS | 6 | NULL | NULL | NULL | NULL | NULL | 6.87 | NULL | NULL | NULL | NULL | NULL |
| 9002-6050 | AWBWA | NULL | NULL | 4 | NULL | NULL | NULL | NULL | NULL | 5.97 | NULL | NULL | NULL |
| 9002-6050 | BUNBURY | NULL | NULL | NULL | 6 | NULL | NULL | NULL | NULL | NULL | 5.882 | NULL | NULL |
| 9002-6050 | BUNBURY | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | 5.883 | NULL | NULL | NULL |
| 9002-6050 | CBCBROAD | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 9002-6050 | CBCBROAD | NULL | NULL | 7 | NULL | NULL | NULL | NULL | NULL | 5.731 | NULL | NULL | NULL |
| 9002-6050 | CBCBROAD | 16 | NULL | NULL | NULL | NULL | NULL | 5.732 | NULL | NULL | NULL | NULL | NULL |
I would like to have one line per item and Customer Combiniation..
Do you have any suggestions on this?
Thanks
Scotty
|||
Yes.. PIVOT is one of the nice operator. But your case it become hard. PIVOT operator is logically perfect if you want to pivot single column.
I tried to convert my previous query using PIVOT.
Create Table #data (
Item Varchar(100) ,
Year Varchar(100) ,
Month int ,
ucivqa01 int ,
AVGPRICE float
);
Insert Into #data Values('ABC','2007','1','10','2.00');
Insert Into #data Values('ABC','2007','2','10','2.10');
Insert Into #data Values('ABC','2007','3','10','2.05');
Insert Into #data Values('ABC','2007','4','10','2.30');
Insert Into #data Values('ABC','2007','5','10','5.00');
Insert Into #data Values('ABC','2007','6','10','1.95');
Insert Into #data Values('XYZ','2007','1','10','100');
Insert Into #data Values('XYZ','2007','2','10','112');
Insert Into #data Values('XYZ','2007','3','10','111');
Insert Into #data Values('XYZ','2007','4','10','100');
Insert Into #data Values('XYZ','2007','5','10','105');
Insert Into #data Values('XYZ','2007','6','10','104');
Code Snippet
Select * From
(
Select * From
(Select Item,Cast(Year as varchar) + '-' + Cast(Month as varchar) YearMonth,ucivqa01 From #data) as Data1
PIVOT
(
Max(ucivqa01) For YearMonth in ([2007-1], [2007-2], [2007-3], [2007-4], [2007-5], [2007-6])
) Pvt1
) as MaxData
Join
(
Select * From
(Select Item,Cast(Year as varchar) + '-' + Cast(Month as varchar) YearMonth,AVGPRICE From #data) as Data2
PIVOT
(
Avg(AVGPRICE) For YearMonth in ([2007-1], [2007-2], [2007-3], [2007-4], [2007-5], [2007-6])
) Pvt2
) as AVGData
on MaxData.Item=AVGData.Item
