Multiple aggregates using PIVOT

Question

Post reply

Multiple aggregates using PIVOT

Hariharan-202345

SSC Eights!

Points: 821

August 28, 2007 at 9:03 am

#174477

Hi all,

Am working on a reporting project which extensively uses the backend stored procedures. We're using SQL 2005 as database.

I have a table as shown below:

Product Name	Part Number	BOM Date	Qty	Extended Cost
BOM1	SA2135A-5163	11/10/2005 0:00	1	1.50000
BOM1	T673ZG	11/10/2005 0:00	1	2.50000
BOM1	4870370A49	11/10/2005 0:00	2	1.25000
BOM1	0109060A58	11/10/2005 0:00	1	1.00000
BOM1	4809948D30	11/10/2005 0:00	1	1.16000
BOM1	2409414M42	11/10/2005 0:00	1	0.02790
BOM1	2409154M62	11/10/2005 0:00	1	0.00486
BOM1	2409154M64	11/10/2005 0:00	1	0.00486
BOM1	2409154M36	11/10/2005 0:00	1	NULL
BOM1	2409154M51	11/10/2005 0:00	1	0.00486
BOM2	SA2616A-7520	5/10/2007 0:00	1	NULL
BOM2	5688653L77	5/10/2007 0:00	0.1	0.03222
BOM2	SNN5696B	5/10/2007 0:00	1	2.84225
BOM2	6802919J03	5/10/2007 0:00	1	NULL
BOM2	SHN8962A	5/10/2007 0:00	1	0.86545
BOM2	1189975Y01	5/10/2007 0:00	1	NULL
BOM2	1590167N05	5/10/2007 0:00	1	0.98714
BOM2	SKN6371C	5/10/2007 0:00	1	0.24500
BOM2	3087629N03	5/10/2007 0:00	1	NULL
BOM2	T676KD	5/10/2007 0:00	1	NULL

The desired output is:

	BOM1		BOM2
Part Number	Sum of Qty	Sum of Extended Cost	Sum of Qty	Sum of Extended Cost
1189975Y01	1	0	1	3
1590167N05	1	0.987142861	1	1.5
SHN8962A	1	0.865450025	1	2
SA3010A-6382	1	0	1	1
5664750E62	0.05	0.044423676	0.05	1.25
CHUG1422BD	1	0	1	1.3
SJUG0866CA	1	0	1	0
7109003A50	1	0	1	1
SHN8965A	1	0	1	1
1487942Y01	1	0.035	1	0.035

There needs to be 2 aggregate columns for each BOM. I tried to acheive the partial result with the query as shown below:

SELECT [PART NUMBER],[BOM DATE],[BOM1],[BOM2]
FROM
(
SELECT [PRODUCT NAME],[PART NUMBER],[BOM DATE],[QUANTITY]
FROM [CURRENT BOM] WHERE [PRODUCT NAME] IN ('BOM1','BOM2')
) AS DATA

PIVOT (SUM([QUANTITY]) FOR [PRODUCT NAME] IN ([BOM1],[BOM2])) AS PVT

But in the PIVOT option we can only specify one aggregate column. How do I get two aggregate columns? something like SUM([QUANTITY]),SUM([EXT_COST])..

I know its not allowed, but I would like to know if there is any work around.

I'm really stuck with this issue and not able to get any help from the web.

I'd really appreciate if any of you can help on this at the earliest.

Tonnes of thanks in advance,

Hari

Kindest Regards,

Hari

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

SELECT

[Part Number],

SUM(CASE WHEN Product = 'BOM1' THEN ISNULL(Qty, 0) ELSE 0 END) AS Bom1Qty,

SUM(CASE WHEN Product = 'BOM1' THEN ISNULL([Extended Cost], 0) ELSE 0 END) AS Bom1Cost,

SUM(CASE WHEN Product = 'BOM2' THEN ISNULL(Qty, 0) ELSE 0 END) AS Bom2Qty,

SUM(CASE WHEN Product = 'BOM2' THEN ISNULL([Extended Cost], 0) ELSE 0 END) AS Bom2Cost

FROM Table1

GROUP BY [Part Number]

ORDER BY [Part Number]

N 56°04'39.16"
E 12°55'05.25"

Hariharan-202345 SSC Eights! Points: 821 More actions · Answer 2

I don't want to use CASE statement. I'd like to know how to do this using PIVOT.

Kindest Regards,

Hari

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

Good luck!

Most often, the CASE statements are faster than the PIVOT too, in my experience.

N 56°04'39.16"
E 12°55'05.25"

robert dragoi SSC Enthusiast Points: 176 More actions · Answer 4

A combination between the 2 solution can do as well :>)

SELECT *

FROM

(

SELECT [PRODUCT_NAME],[PART_NUMBER],[Qty],

sum(case when Product_Name = 'BOM1' then isnull(Extended_Cost,0) else 0 end ) as Bom1Cost,

sum(case when Product_Name = 'BOM2' then isnull(Extended_Cost,0) else 0 end ) as Bom2Cost

FROM [CURRENTBOM] WHERE [PRODUCT_NAME] IN ('BOM1','BOM2')

group by [PRODUCT_NAME],[PART_NUMBER],[Qty]

) AS DATA

PIVOT

(SUM([Qty]) FOR [PRODUCT_NAME] IN ([BOM1],[BOM2])) as PVT1

order by

Part_Number

Hariharan-202345 SSC Eights! Points: 821 More actions · Answer 5

thanks for the reply. the problem is the number of bom names used in the query is dynamic ie., the product names are not limited to 2. even the query i have in my post is a dynamically formed sql statement.

Kindest Regards,

Hari

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 6

Why didn't you say so from the beginning?

Read my article here http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

It has everything you need, including multiple aggregations.

N 56°04'39.16"
E 12°55'05.25"

samjoseph_p SSC Enthusiast Points: 179 More actions · Answer 7

SwePeso (8/28/2007)

SELECT [Part Number],
SUM(CASE WHEN Product = 'BOM1' THEN ISNULL(Qty, 0) ELSE 0 END) AS Bom1Qty,
SUM(CASE WHEN Product = 'BOM1' THEN ISNULL([Extended Cost], 0) ELSE 0 END) AS Bom1Cost,
SUM(CASE WHEN Product = 'BOM2' THEN ISNULL(Qty, 0) ELSE 0 END) AS Bom2Qty,
SUM(CASE WHEN Product = 'BOM2' THEN ISNULL([Extended Cost], 0)ELSE 0 END) AS Bom2Cost
FROM Table1
GROUP BY [Part Number]
ORDER BY [Part Number]

😀

samjoseph_p SSC Enthusiast Points: 179 More actions · Answer 8

samjoseph_p

SSC Enthusiast

Points: 179

April 13, 2014 at 7:37 am

#1705591

It works somehow.