This article documents a solution to the problem raised in
the following thread and explains the pitfalls people often encounter with the PIVOT function:
[STC_Header_V_2].[STCID] = [STC_Detail].[STCID]
[STC_Code].[STCDTLID] = [STC_Detail].[STCDTLID]
[Code_V_2].[CodeID] = [STC_Code].[CodeID]
'Time Period Qualifier'
'Cost Share Type'
'Insurance Certificate Code'
'Quantity Qualifier Code'
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.
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.