Tuesday, January 19, 2010

PIVOT IN Multipal Column


DECLARE @Table TABLE ( OrderId INT, ItemId INT, ItemQty INT, ItemValue INT)

INSERT INTO @Table VALUES (1,1,1,10)
INSERT INTO @Table VALUES (1,2,2,20)
INSERT INTO @Table VALUES (1,3,3,30)

INSERT INTO @Table VALUES (2,1,1,11)
INSERT INTO @Table VALUES (2,2,2,21)
INSERT INTO @Table VALUES (2,3,3,31)

INSERT INTO @Table VALUES (3,1,1,12)
INSERT INTO @Table VALUES (3,2,2,22)
INSERT INTO @Table VALUES (3,3,3,32)

SELECT
OrderId
, SUM([Q1]) AS Q1
, SUM([Q2]) AS Q2
, SUM([Q3]) AS Q3
, SUM([V1]) AS V1
, SUM([V2]) AS V2
, SUM([V3]) AS V3
FROM
(
SELECT
OrderId
, 'Q' + CAST( ItemId AS VARCHAR(10) ) AS QtyItemCd
, ItemQty
, 'V' + CAST( ItemId AS VARCHAR(10) ) AS ValueItemCd
, ItemValue
FROM @Table
) AS OrderDetail
PIVOT ( SUM(ItemQty) FOR QtyItemCd IN ( [Q1], [Q2], [Q3] ) ) AS pvtQty
PIVOT ( SUM(ItemValue) FOR ValueItemCd IN ( [V1], [V2], [V3] ) ) AS pvtValue
GROUP BY OrderId
ORDER BY OrderId

No comments: