UNPIVOT in TSQL

Quiz: UNPIVOT in TSQL

FYI, it's perfectly fair to copy and paste the queries and test them before answering. These questions are based on the following data:
CREATE TABLE #performers (
    [πŸ‘‘] varchar(256),
    birthplace varchar(256),
    residence varchar(256)
);
INSERT #performers ([πŸ‘‘], birthplace, residence )
    VALUES 
        ('Bob', 'Georgia', 'NY'),
        ('Nina Bo''Nina', 'Georgia',  'Georgia'),
        ('Phi Phi', 'Texas', 'NYC');
  • SELECT 
        *
    FROM 
        ( SELECT birthplace, residence
        FROM #performers
        WHERE [πŸ‘‘] = 'Bob'
        ) as unpivsrc
    UNPIVOT 
        (placename for place in (birthplace, residence)) 
        as unpiv;
  • SELECT 
        *
    FROM 
        ( SELECT birthplace, residence
        FROM #performers
        WHERE [πŸ‘‘] = 'Bob'
        ) as unpivsrc
    UNPIVOT 
        (placename for place in (birthplace, residence)) 
        as unpiv;
    
  • SELECT 
        *
    FROM 
        ( SELECT [πŸ‘‘], birthplace, residence
        FROM #performers
        WHERE [πŸ‘‘] = 'Bob'
        ) as unpivsrc
    UNPIVOT 
        (placename for place in (birthplace, residence)) 
        as unpiv;
    
  • SELECT 
        *
    FROM 
        ( SELECT birthplace, residence
        FROM #performers
        WHERE residence = 'NYC'
        ) as unpivsrc
    UNPIVOT 
        (placename for place in (birthplace, residence)) 
        as unpiv;
    
    SELECT 
        *
    FROM 
        ( SELECT birthplace, residence
        FROM #performers
        ) as unpivsrc
    UNPIVOT 
        (placename for place in (birthplace, residence)) 
        as unpiv
    WHERE placename = 'NYC';

2 thoughts on “UNPIVOT in TSQL

  1. Nice quiz on unpivot.
    In spirit the last quiz also had an unpivot element to it using cross apply. In my work I find that when I need to unpivot something I don’t care about the labels too much so I opt for the cross apply method.
    Also enjoyed you working in the emoji as a column name. Should see more reports with emoji headers, it’s the language of the future.

    1. Yes! That can work really well, I agree.

      I’ve also found some cases where I could reshape the data with a group by/ aggregate instead of the unpivot before then pivoting the data, and that was MUCH speedier because it reduced the rowcount a lot before pivoting.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.