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.