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

    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 '
        [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'
    FOR CODE_CAT            IN
    )) 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

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
  • Naomi  N edited Revision 1. Comment: Adjusted code block

  • Naomi  N edited Revision 4. Comment: Adjusted participation link

  • Naomi  N edited Revision 5. Comment: Added tag

  • Naomi  N edited Revision 7. Comment: Added See Also

  • Naomi  N edited Revision 8. Comment: Formatting

  • Ed Price - MSFT edited Revision 9. Comment: White space, capitalization, punctuation, tags, and consistent font sizes. Great article!

  • Ed Price - MSFT edited Revision 10. Comment: Have to revert. An edit messes up the HTML color and code block.

  • Naomi  N edited Revision 15. Comment: Added toc and See Also section

  • Naomi  N edited Revision 16. Comment: Formatting

  • Naomi  N edited Revision 17. Comment: Formatting

Page 1 of 2 (12 items) 12
Wikis - Comment List
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Page 1 of 2 (16 items) 12