Problem Definition

This article documents a solution to the problem raised in the following thread and explains the pitfalls people often encounter with the PIVOT function:

;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.  The thread originator was having problems arriving at it. The main issue is to understand, that all columns which are not mentioned in the PIVOT aggregate function in the PIVOT clause, will be aggregated.  

Something to watch out for particularly is a column with unique values in the source table for the pivot that is not listed in the PIVOT clause.  Such a column will be a source of the aggregation and therefore the result will have as many rows as you have unique values in the column -  thus defeating the main purpose of the PIVOT. This subtle issue can be totally mystifying unless you are aware of the cause.

Relatively easy to resolve once you are aware.
 


Other Blogs

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

and also my own blog post which is a bit advanced:

This entry participated in the TechNet Guru Contribution for May, 2013 contest and won the Gold prize.

 


See Also

  


Other Languages