CODEWITHSUNDEEP
sqlsql-server
TheDProgrammer
TheDProgrammer

Reputation: 95

SQL Query to combine results and show in a PIVOT

Below mentioned are the two of my queries:

SELECT  WINDOWS_NT_LOGIN, COUNT(DPS_NUMBER) as TotalDPS
FROM DispatcherProductivity
WHERE DPS_Processed_Time_Stamp>='12/04/2014 10:30 AM' 
AND DPS_Processed_Time_Stamp<='12/05/2014 10:30 AM'
GROUP BY WINDOWS_NT_LOGIN
ORDER BY TotalDPS

SELECT  STATUS, COUNT(DPS_NUMBER) AS TotalDPS
FROM DispatcherProductivity
WHERE DPS_Processed_Time_Stamp>='12/04/2014 10:30 AM' 
AND DPS_Processed_Time_Stamp<='12/05/2014 10:30 AM'
GROUP BY STATUS
ORDER BY TotalDPS

Their respective Results are:

WINDOWS_NT_LOGIN    TotalDPS
A_S 72
T_I_S   133

STATUS  TotalDPS
ID  1
Can 2
NHD 3
SED 14
Ord 185

I would like to get the results in this format:

WINDOWS_NT_LOGIN ID Can NHD SED Ord
A_S                         2   70
T_I_S            1  2   3   12  115

Thanks

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I tend to use conditional aggregations for pivots. In this case:

SELECT WINDOWS_NT_LOGIN, COUNT(DPS_NUMBER) as TotalDPS,
       SUM(CASE WHEN status = 'ID' THEN DPS_Number END) as ID,
       SUM(CASE WHEN status = 'Can' THEN DPS_Number END) as Can,
       SUM(CASE WHEN status = 'NHD' THEN DPS_Number END) as NHD,
       SUM(CASE WHEN status = 'SED' THEN DPS_Number END) as SED,
       SUM(CASE WHEN status = 'Ord' THEN DPS_Number END) as Ord
FROM DispatcherProductivity
WHERE DPS_Processed_Time_Stamp >= '12/04/2014 10:30 AM' AND
      DPS_Processed_Time_Stamp <= '12/05/2014 10:30 AM'
GROUP BY WINDOWS_NT_LOGIN;

I would also recommend that you use YYYY-MM-DD format for your dates. I, for one, don't know if your dates are for December or April and May.

Upvotes: 0

GarethD
GarethD

Reputation: 69829

You can use the PIVOT function for this:

SELECT  pvt.WINDOWS_NT_LOGIN,
        pvt.[ID],
        pvt.[Can],
        pvt.[NHD],
        pvt.[SED],
        pvt.[Ord]
FROM    (   SELECT  WINDOWS_NT_LOGIN, STATUS, DPS_NUMBER
            FROM    DispatcherProductivity
            WHERE   DPS_Processed_Time_Stamp>='20141204 10:30:00' 
            AND     DPS_Processed_Time_Stamp<='20141205 10:30:00'
        ) AS t
        PIVOT
        (   COUNT(DPS_NUMBER)
            FOR STATUS IN ([ID], [Can], [NHD], [SED], [Ord])
        ) AS pvt;

N.B. I changed your dates to the culture invariant format yyyyMMdd hh:mm:ss, however, I was not sure if 12/04/2014 was supposed to tbe 12th April, or 4th December (the exact problem with that format), so it is possible I have put the wrong dates in. I assumed you meant 4th December as that is today.

For further reading read Bad habits to kick : mis-handling date / range queries

Upvotes: 1

Related Questions