SQL Server PIVOT

SQL Server PIVOT

 

Problem Definition

 
Recently in this thread I helped to solve a relatively simple problem. I will quote my solution and then I will explain the main problem people often encounter with PIVOT solutions

;WITH   CTE_STC_DETAIL_CODES    AS
(
    SELECT
            [Code_V_2].[CODE_CAT],
          
            [Code_V_2].[DESCRIPTION]
    FROM    [dbo].[STC_Detail]
    INNER JOIN  [STC_Header_V_2]              
            ON  [STC_Header_V_2].[STCID]    =       [STC_Detail].[STCID]
    INNER JOIN  [STC_Code]            
            ON  [STC_Code].[STCDTLID]       =       [STC_Detail].[STCDTLID]
    INNER JOIN  [Code_V_2]            
            ON  [Code_V_2].[CodeID]             =       [STC_Code].[CodeID]
    WHERE   [STC_Header_V_2].[STC]          =       '33 '
)
  
SELECT  [STCDTLID],
        [SN]                                    AS  'Sub Net',
        [NT]                                    AS  'Network Indicator',
        [CV]                                    AS  'Coverage Level',
        [TQ]                                    AS  'Time Period Qualifier',
        [AI]                                    AS  'Authorization Indicator',
        [CS]                                    AS  'Cost Share Type',
        [IC]                                    AS  'Insurance Certificate Code',
        [QQ]                                    AS  'Quantity Qualifier Code'
FROM    CTE_STC_DETAIL_CODES
PIVOT
(
    MAX([DESCRIPTION])
    FOR CODE_CAT            IN
    (
    [SN],
    [NT],
    [CV],
    [TQ],
    [AI],
    [CS],
    [IC],
    [QQ]
    )) AS Pvt


Common Problem


The pivot solution by itself is not complex, it is a simple static PIVOT. But the thread originator was having a problem arriving to it. The main problem is to understand, that all columns which are not mentioned in the PIVOT aggregate function in the PIVOT clause will be aggregated, so if there is a column with unique values in the source table for the pivot and it is not mentioned in the PIVOT clause, it will be a source of the aggregation and therefore the result will have as many rows as you have unique columns in the table defeating the main purpose of the PIVOT.

This is something I wanted to emphasize. 

Other Blogs



There are two blog posts that may help understanding PIVOT better:

Understanding SQL Server 2000 Pivot with Aggregates

by George Mastros

and also my own blog post which is a bit advanced

Dynamic PIVOT on multiple columns

This entry participated in the Technology Guru TechNet WiKi for May contest and won the Gold prize. 


See Also


Other Languages

Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (9 items)