Crypticlight Crypticlight - 7 months ago 17
SQL Question

Suggestions to modify pivot query in SQL Server to get desired output

I have following data:

------------------------------------------------
DestinationSite SourceSite Dist Rank
------------------------------------------------
CZ_0 PFC 2717.64 1
CZ_0 WFC 3218.22 2
CZ_0 OFC 3971.14 3
CZ_0 FFC 4226.69 4
CZ_10001 FFC 64.13 1
CZ_10001 OFC 564.11 2
CZ_10001 WFC 2511.03 3
CZ_10001 PFC 2867.75 4


And I want to change it to the following format:

------------------------------------------------
DestinationSite Pref1 Pref2 Pref3 Pref4
------------------------------------------------
CZ_0 PFC WFC OFC FFC
CZ_10001 FFC OFC WFC PFC


I wrote the query as below:

select DestinationSite, [1] as Pref1, [2] as Pref2, [3] as Pref3, [4] as Pref4
from Table1 a
pivot (max(SourceSite) for Rank in ([1],[2],[3],[4])) as b
order by DestinationSite;


But it gives me output as below, different than what I require:

-------------------------------------------------
DestinationSite Pref1 Pref2 Pref3 Pref4
-------------------------------------------------
CZ_0 PFC NULL NULL NULL
CZ_0 NULL WFC NULL NULL
CZ_0 NULL NULL OFC NULL
CZ_0 NULL NULL NULL FFC
CZ_10001 FFC NULL NULL NULL
CZ_10001 NULL OFC NULL NULL
CZ_10001 NULL NULL WFC NULL
CZ_10001 NULL NULL NULL PFC


Need suggestions on how to modify my query to get the desired output. Thanks.

Answer

Try this:

Create table

create table Destination 
(
 DestinationSite varchar(10),
 SourceSite varchar(3),
 dist decimal(18,2),
 [rank] tinyint
 )

Insert data

insert into Destination values

('CZ_0',    'PFC', 2717.64,   1),
('CZ_0',    'WFC', 3218.22,   2),
('CZ_0',    'OFC', 3971.14,   3),
('CZ_0',    'FFC', 4226.69,   4),
('CZ_10001','FFC', 64.13,     1),
('CZ_10001','OFC', 564.11,    2),
('CZ_10001','WFC', 2511.03,   3),
('CZ_10001','PFC', 2867.75,   4)

Pivot

; with cte
as
(
  select
         DestinationSite,
         SourceSite,
         [rank] as r
   from Destination

)

SELECT DestinationSite,[1] as Pref1 ,[2] as Pref2,[3] as Pref3 ,[4] as Pref4 FROM cte
PIVOT(max(SourceSite) FOR r IN ([1],[2],[3],[4])  ) AS P

Result

------------------------------------------------
DestinationSite   Pref1   Pref2   Pref3   Pref4
------------------------------------------------
CZ_0               PFC     WFC     OFC     FFC
CZ_10001           FFC     OFC     WFC     PFC