Pivot On Multiple Columns

------------------------------------------------------------------------------

-- temp table for the example

CREATE TABLE tmpResults

( CustomerType nvarchar(50),

SomeTypeLetter nvarchar(50),

NumOfCustomers int

)

INSERT INTO tmpResults (CustomerType, SomeTypeLetter, NumOfCustomers)

VALUES ('CustomerType1', 'SomeTypeA', 33),

('CustomerType1', 'SomeTypeB', 12),

('CustomerType1', 'SomeTypeC', 61),

('CustomerType2', 'SomeTypeA', 11),

('CustomerType2', 'SomeTypeB', 25),

('CustomerType2', 'SomeTypeC', 21),

('CustomerType3', 'SomeTypeA', 17),

('CustomerType3', 'SomeTypeB', 38),

('CustomerType3', 'SomeTypeC', 9),

('CustomerType4', 'SomeTypeA', 5),

('CustomerType4', 'SomeTypeB', 91),

('CustomerType4', 'SomeTypeC', 12)

--SELECT * FROM tmpResults

------------------------------------------------------------------------------

--Pivot On Multiple Columns:

SELECT [CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

FROM 

(   SELECT  isnull(p.NumOfCustomers,0) as NumOfCustomers,

(p.CustomerType) + '-' + (p.SomeTypeLetter) as [desc]

FROM    tmpResults p

) [base]

PIVOT

(   SUM(NumOfCustomers)

FOR [desc] IN

(

[CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

)

) [pvt]

DROP TABLE tmpResults