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


Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 19. Comment: Minor edit

  • Naomi  N edited Revision 18. Comment: Changed first to gold

  • Naomi  N edited Revision 17. Comment: Formatting

  • Naomi  N edited Revision 16. Comment: Formatting

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

  • 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 8. Comment: Formatting

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

  • Naomi  N edited Revision 5. Comment: Added tag

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