PIVOT TABLE QUERY

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