Grouping and Windowing - Part 3

So another part of Grouping and Windowing in SQL Server is Pivoting and Unpivoting

Pivoting 

I am going to use this dataset for this example

Using this query

WITH PivotData AS

(

SELECT

Type , -- grouping column

Column1, -- spreading column

Number -- aggregation column

FROM [dbo].[Pets]

)

SELECT Type, [DOGS], [CAT], [FISH]

FROM PivotData

PIVOT(SUM(Number) FOR Column1 IN ([DOGS],[CAT],[FISH]) ) AS P;

produces this result. By looking at the code above, you can work out what is going on from the comments.

Unpivoting 

Using this dataset from the previous section 

We can unpivot by using this code.

SELECT Column1,Number,[Type]

FROM PivotData2

UNPIVOT( Number FOR Column1 IN([DOGS],[CAT],[FISH]) ) AS U;

and it will produce these results