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!
x
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