PIVOT / UNPIVOT

/****************** PIVOT SALES DATA *******************/
;WITH CTE
AS (SELECT YEAR = YEAR(orderDate),
    QUARTER = DatePart(qq,OrderDate),
    Sales = Sum(TotalDue)
    FROM Sales.SalesOrderHeader
    GROUP BY YEAR(orderDate), DatePart(qq,OrderDate))


SELECT YEAR, Q1 = [1], Q2 = [2], Q3 = [3], Q4 = [4]
INTO #PivotTable
FROM (SELECT * FROM CTE) AS PivotInput
PIVOT
(SUM(Sales) -- Aggregate for cells
FOR QUARTER IN ( [1],[2],[3],[4] ) ) AS PivotOutput
ORDER BY YEAR;

SELECT * FROM #PivotTable
GO
                   
/****************** UNPIVOT DATA JUST PIVOTED *******************/  
SELECT      YEAR,
            Quarter,
            Sales
FROM (      SELECT *
            FROM #PivotTable) p
UNPIVOT
(Sales
 FOR Quarter IN ( [Q1],[Q2],[Q3],[Q4] ) ) AS unpvt
 ORDER BY YEAR,
          Quarter;
GO

-- SQL UNPIVOT using the UNION ALL operator

-- SQL change columns into rows

/* INPUT DATA in CTE

SalesOrderID      OrderDate   Sales       AccountNumber

43659             2001-07-01  $23,153.23  10-4020-000676

43660             2001-07-01  $1,457.33   10-4020-000117

43661             2001-07-01  $36,865.80  10-4020-000442

43662             2001-07-01  $32,474.93  10-4020-000227

......

*/
;WITH cteSO AS

(SELECT SalesOrderID, OrderDate = convert(date,OrderDate),

        Sales='$'+convert(varchar,TotalDue,1), AccountNumber

FROM AdventureWorks.Sales.SalesOrderHeader)

SELECT SalesOrderID,Attribute='OrderDate', aValue=convert(varchar,OrderDate)

FROM cteSO

UNION ALL

SELECT SalesOrderID,Attribute='Sales', aValue=Sales FROM cteSO

UNION ALL

SELECT SalesOrderID,Attribute='AccountNumber', aValue=AccountNumber FROM cteSO

ORDER BY SalesOrderID, Attribute

/*

SalesOrderID      Attribute               aValue

43659             AccountNumber           10-4020-000676

43659             OrderDate               2001-07-01

43659             Sales                   $23,153.23

43660             AccountNumber           10-4020-000117

43660             OrderDate               2001-07-01

43660             Sales                   $1,457.33

*/

-- CROSS TAB

SELECT pvt.*

FROM   (SELECT Category,

               Size,

               SizeExist = 1

        FROM   SubCategorySizeUsage

        UNION

        SELECT x.Category,

               y.Size,

               0

        FROM   SubCategorySizeUsage x

               CROSS JOIN SubCategorySizeUsage y) cc

       PIVOT

       (sum(SizeExist)

        FOR Size IN (  [38], [40], [42], [44], [46], [48], [50], [52], [54], [56], [58], [60], [62], [70], [L], [M], [S], [XL]) ) AS pvt