Re-Formatting Final Result Set in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 454
So many times i faced this type of re-formatting SQL result set for better looking/requirement... That is the reason i wanted to post the below code snippet with sample ipput and output

--Sample Result of one query
ProjectName SellingAgents HoldLotNo EOILotNo CILotNo
P1 Agent1 10 NULL NULL
P1 Agent1 11 NULL NULL
P1 Agent1 NULL 13 NULL
P1 Agent1 NULL 45 NULL
P1 Agent1 NULL NULL 109


-- The expected output 
ProjectName SellingAgents HoldLotNo EOILotNo CILotNo
P1 Agent1 10 13 NULL
P1 Agent1 11 45 109


--Re-Formatting Final Result Set in SQL Server
DECLARE @Temp TABLE (
ProjectName VarChar(500),
SellingAgents VarChar(500),
HoldLotNo VarChar(50),
EOILotNo VarChar(50),
CILotNo VarChar(50)
)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','10',null,null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1','11',null,null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,'13',null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,'45',null)
Insert into @temp (ProjectName,SellingAgents,HoldLotNo,EOILotNo,CILotNo)
values ('P1','Agent1',null,null,'109')


;With CTE
AS
(SELECT ProjectName,SellingAgents
,HoldLotNo
,EOILotNo
,CILotNo
,CASE WHEN HoldLotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY HoldLotNo) END AS SeqHoldLotNo,
CASE WHEN EOILotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY EOILotNo) END AS SeqEOILotNo,
CASE WHEN CILotNo IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY ProjectName,SellingAgents ORDER BY CILotNo) END AS SeqCILotNo
FROM @Temp
)
SELECT c1.ProjectName,c1.SellingAgents,c1.HoldLotNo,c2.EOILotNo,c3.CILotNo
FROM CTE c1
LEFT OUTER JOIN CTE c2
ON c2.ProjectName = c1.ProjectName
AND c2.SellingAgents = c1.SellingAgents
AND c2.SeqEOILotNo = c1.SeqHoldLotNo
AND c2.SeqEOILotNo IS NOT NULL
LEFT OUTER JOIN CTE c3
ON c3.ProjectName = c1.ProjectName
AND c3.SellingAgents = c1.SellingAgents
AND c3.SeqCILotNo = c1.SeqHoldLotNo
AND c3.SeqCILotNo IS NOT NULL
WHERE c1.SeqHoldLotNo IS NOT NULL

Comments or Responses

Login to post response