Pivot and Multi Columns

[andret503]

Just for fun I ran across a bit on multi columns with Pivot. Here's a solution I came up with. Feel free to improve upon the example. The purpose was to fiddle around with Common Table Expressions, etc. Note: The datasource for this was Northwind, thus the 1996,7 and 8 dates...


With pvtConcat
AS
(
SELECT
ShipCity
, [1996] as C1
, [1997] as C2
, [1998] as C3

From
(SELECT
ShipCity
, YEAR(OrderDate) AS OrderYear
--, [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
, Count(OrderID) as OrderCount
FROM Orders
Group By ShipCity, YEAR(OrderDate)
) AS PVT
PIVOT
(
Sum(OrderCount) FOR OrderYear IN([1996],[1997],[1998])
) as Child
)

Select ShipCity, C1, C2, C3
into #_concat
from pvtConcat;

With pvtMerge
AS
(
SELECT
ShipCity
, [1996] as Y1
, [1997] as Y2
, [1998] as Y3

From
(SELECT
Orders.ShipCity
, YEAR(Orders.OrderDate) AS OrderYear
, [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
) AS PVT
PIVOT
(
Sum(OrderTotal) FOR OrderYear IN([1996],[1997],[1998])
) as Child
)
select * into #_merge from pvtMerge;

Select cc.ShipCity, cc.C1, cc.C2, cc.C3, pm.Y1, pm.Y2, pm.Y3 from #_concat cc Inner Join #_merge pm on cc.ShipCity = pm.ShipCity

 

[markros]

For pivoting on multiple columns it's better to use case based pivot (old style pivoting) for performance reasons.

See this blog post for dynamic pivot on multiple columns (it references CASE based pivot blog):

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns

PluralSight Learning Library