Skip to content
Advertisement

Compress the pivot table result to one row in SQL

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
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement