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

Comments

  1. This seems like it was a perfect study aid. Well done.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. event management. The main advantage of VR events over classic virtual events is pretty straightforward also It allows attendees/users to feel like they're in a physical environment with other people without actually being in the same physical space and which is clearly more immersive than interacting with others on a computer screen. goodie bag ideas and best places to sell concert tickets

    ReplyDelete
  4. Thank you for sharing an amazing & wonderful blog. This content is very useful, informative and valuable in order to enhance knowledge. Keep sharing this type of content with us & keep updating us with new blogs. Apart from this, if anyone who wants to join the Data Science Training institute in Delhi, you can contact 9311002620 or visit our website-
    https://htsindia.com/Courses/python/python-with-data-science-training-course

    ReplyDelete

Post a Comment

Popular posts from this blog

My First Year as a Data Scientist

My First Retro