I want to have a single line result in SQL pivot table like below
+------------+-----------+-----------+----------+ | Request_id | IT Review | SO Review | Approved | +------------+-----------+-----------+----------+ | 11111 | 09-19 | 09-20 | 09-21 | +------------+-----------+-----------+----------+
However, I can’t get rid of the null value in the columns:
+------------+-----------+-----------+----------+ | Request_id | IT Review | SO Review | Approved | +------------+-----------+-----------+----------+ | 11111 | 09-19 | NULL | NULL | | 11111 | NULL | 09-20 | NULL | | 11111 | NULL | NULL | 09-21 | +------------+-----------+-----------+----------+
Below is my query, could anyone please shed some lights on how to revise my query? Thanks!
SELECT [Request_ID] [Request ID] ,[IT Review] ,[SO Review] ,[Approved] FROM ( SELECT * FROM [RptChange].[dbo].[tRequestsAudit] a JOIN ( SELECT [reqID] [Request_ID] ,MIN([reqUpdateDt]) [UpdateDT] ,MIN([AuditID]) AS EarliestAuditID ,[reqStatus] SubStatus FROM [RptChange].[dbo].[tRequestsAudit] WHERE [reqID] = 102943 GROUP BY [reqStatus] ,[reqID] ) sub ON sub.[EarliestAuditID] = a.AuditID ) sub2 PIVOT(MIN([UpdateDT]) FOR sub2.reqStatus IN ( [IT Review] ,[SO Review] ,[Approved] )) AS P
Advertisement
Answer
Try replace
SELECT *
with
SELECT DISTINCT sub.Request_ID, sub.UpdateDT,a.reqStatus
Something like that:
SELECT [Request_ID] [Request ID] ,[IT Review] ,[SO Review] ,[Approved] FROM ( SELECT DISTINCT sub.Request_ID, sub.UpdateDT,a.reqStatus FROM [RptChange].[dbo].[tRequestsAudit] a JOIN ( SELECT [reqID] [Request_ID] ,MIN([reqUpdateDt]) [UpdateDT] ,MIN([AuditID]) AS EarliestAuditID ,[reqStatus] SubStatus FROM [RptChange].[dbo].[tRequestsAudit] WHERE [reqID] = 102943 GROUP BY [reqStatus] ,[reqID] ) sub ON sub.[EarliestAuditID] = a.AuditID ) sub2 PIVOT(MIN([UpdateDT]) FOR sub2.reqStatus IN ( [IT Review] ,[SO Review] ,[Approved] )) AS P