This will do it. If you want the NULLs to show up instead of the blanks, just remove the ELSE '' from each line where it appears.

WITH

cteCrossTab AS

(

SELECT Customer,

Tariff1 = MAX(CASE WHEN TariffOrder = 1 THEN Tariff ELSE '' END),

Tariff2 = MAX(CASE WHEN TariffOrder = 2 THEN Tariff ELSE '' END),

Tariff3 = MAX(CASE WHEN TariffOrder = 3 THEN Tariff ELSE '' END),

Tariff4 = MAX(CASE WHEN TariffOrder = 4 THEN Tariff ELSE '' END)

FROM #Cust

GROUP BY Customer

)

SELECT [Count] = COUNT(*),

Tariff1, Tariff2, Tariff3, Tariff4

FROM cteCrossTab

GROUP BY Tariff1, Tariff2, Tariff3, Tariff4

ORDER BY Tariff1, Tariff2, Tariff3, Tariff4

;

Results from the given data in the original post:

Count Tariff1 Tariff2 Tariff3 Tariff4

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

3 101

1 101 151 171 171

1 101 171 171

1 121

2 121 171

1 121 171 171

1 171

(7 row(s) affected)