PIVOT and UNPIVOT in T-SQL
I recently passed my 70-761 Querying Data with Transact-SQL exam as part of mt revision I created the following diagram to help me remember how PIVOT and UNPIVOT work. I thought it would be good to share. For completeness, the following gives the syntax to use when using PIVOT and UNPIVOT. WITH PivotData AS ( SELECT <grouping column> , <spreading column> , <aggregation column> FROM <source table> ) SELECT <grouping column>, <distinct spreading values> FROM PivotData PIVOT (<aggregation function>(<aggregation column>) FOR <spreading column> IN <distinct spreading values>)); SELECT <grouping column>, <target names column>, <target values column> FROM <pivoted source table> UNPIVOT(<target values column> FOR <target names column> IN (<distinct spreading values>)); The End