When shall I use which, PIVOT in FROM or GROUP BY outside FROM clause?

Tim 0
Viewed 38

From SQL Server's T-SQL document, the syntax for a PIVOT clause in a FROM cluase is:

[ FROM { <table_source> } [ ,...n ] ]  

<table_source> ::=   
{  
    table_or_view_name [ FOR SYSTEM_TIME <system_time> ] [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML <openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | <joined_table>   
    | <pivoted_table>   
    | <unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]     
}  

<pivoted_table> ::=  
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]  
  
<pivot_clause> ::=  
        ( aggregate_function ( value_column [ [ , ]...n ])   
        FOR pivot_column   
        IN ( <column_list> )   
    )

Is

SELECT *
FROM table_source PIVOT ( aggregate_function ( value_column)   
        FOR pivot_column   
        IN ( value1, value2 )   
    )

equivalent to

SELECT pivot_column, aggregate_function(value_column)
FROM table_source 
WHERE pivot_column = value1 OR pivot_column = value2
GROUP BY pivot_column

?

Are both PIVOT and GROUP BY used for doing the same thing: group records by some columns (and then aggregate over some other columns)?

When shall PIVOT be used, and when shall GROUP BY be used?

Thanks.

group-by pivot tsql sql-server sql
1 Answers

No. It is effectively conditional aggregation, and is equivalent to

SELECT
  other_columns_here,
  value1 = aggregate_function(CASE WHEN pivot_column = value1 THEN value_column END),
  value2 = aggregate_function(CASE WHEN pivot_column = value2 THEN value_column END)
FROM table_source 
GROUP BY
  other_columns_here;

You can see that this is what it's transformed into if you examine the query plan.

As it happens, most good SQL writers do not use PIVOT except for very large value lists, because it's pretty inflexible. It's normally better to just use GROUP BY, although it can be more verbose.

Charlieface0
Related