SQL Server PIVOT
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:
- Understanding SQL Server 2000 Pivot with Aggregates by George Mastros
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
- T-SQL: Dynamic Pivot on Multiple Columns
- Transact-SQL Portal
- [[T-SQL Useful Links]]
Other Languages