Customer	Tariff	TariffOrder
Record1   	121	1
Record1   	171	2
Record1   	171	3
Record2   	121	1
Record2   	171	2
Record3   	121	1
Record4   	101	1
Record5   	101	1
Record5   	151	2
Record5   	171	3
Record5   	171	4
Record6   	121	1
Record6   	171	2
Record7   	101	1
Record8   	101	1
Record8   	171	2
Record8   	171	3
Record9   	101	1
Record10  	171	1

Count	tariff1	tariff2	tariff3	tariff4
3	101	NULL	NULL	NULL
1	101	151	171	171
1	101	171	171	NULL
1	121	NULL	NULL	NULL
2	121	171	NULL	NULL
1	121	171	171	NULL
1	171	NULL	NULL	NULL

create table #Cust
(
 Customer char(10) not null,
 Tariff   char(3) not null,
 TariffOrder tinyint not null
)

insert into #Cust (Customer, Tariff, TariffOrder) values 
 ('Record1', '121', 1),
 ('Record1', '171', 2),
 ('Record1', '171', 3),
 ('Record2', '121', 1),
 ('Record2', '171', 2),
 ('Record3', '121', 1),
 ('Record4', '101', 1),
 ('Record5', '101', 1),
 ('Record5', '151', 2),
 ('Record5', '171', 3),
 ('Record5', '171', 4),
 ('Record6', '121', 1),
 ('Record6', '171', 2),
 ('Record7', '101', 1),
 ('Record8', '101', 1),
 ('Record8', '171', 2),
 ('Record8', '171', 3),
 ('Record9', '101', 1),
 ('Record10', '171', 1)

SELECT Customer, Count=COUNT(*)
    ,tariff1=MAX(CASE TariffOrder WHEN 1 THEN Tariff END)
    ,tariff2=MAX(CASE TariffOrder WHEN 2 THEN Tariff END)
    ,tariff3=MAX(CASE TariffOrder WHEN 3 THEN Tariff END)
    ,tariff4=MAX(CASE TariffOrder WHEN 4 THEN Tariff END)
FROM #Cust
GROUP BY Customer

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
;

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)