Quiz: Pivot

Quiz: PIVOT in TSQL

Psst! You can 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 #performers 
    PIVOT (
        MAX (birthplace)
        FOR [πŸ‘‘] in ([Bob] )
    ) as pvt;
  • SELECT *
    FROM 
    ( SELECT 
        [πŸ‘‘], 
        birthplace
    FROM #performers ) as pvtsrc
    PIVOT (
        MAX (birthplace)
        FOR [πŸ‘‘] in ([Bob], [Nina Bo'Nina], [Phi Phi] )
    ) as pvt;
  • SELECT *
    FROM 
    ( SELECT 
        [πŸ‘‘], 
        birthplace
    FROM #performers ) as pvtsrc
    PIVOT (
        MAX (birthplace)
        FOR [πŸ‘‘] in ([Bob], [Nina Bo'Nina], [Phi Phi] )
    ) as pvt;
    
    SELECT
        MAX(CASE [πŸ‘‘] WHEN 'Bob' then birthplace ELSE '' END) as [Bob],
        MAX(CASE [πŸ‘‘] WHEN 'Nina Bo''Nina' then birthplace ELSE '' END) as [Nina Bo'Nina],
        MAX(CASE [πŸ‘‘] WHEN 'Phi Phi' then birthplace ELSE '' END) as [Phi Phi] 
    FROM #performers;