CODEWITHSUNDEEP
sqlsql-server
Brie
Brie

Reputation: 2359

SQL Combine multiple row fields into 1 record based on different field

I have this table (columns and records truncated for clarity):

StartHour WorkstationName
--------- ---------------
4         Wigets1
19        Wigets2

and I would like to have a 1-record result with just the values from StartHour for these two rows, so my result would be

Wigets1   Wigets2
--------- ---------
4         19

and I have this query right now:

SELECT Wigets1, Wigets2
FROM (SELECT * FROM InspectorApp 
WHERE WorkstationName IN ('Wigets1', 'Wigets2')) innerselect 
PIVOT(MAX(StartHour) FOR WorkstationName IN ([Wigets1], [Wigets2])) pvt

but I get a result of 

EarlyPipe LgLnStart
--------- ---------
NULL      19
4         NULL

How can I get rid of the NULLs here?

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

pivot is finicky. Just choose the columns you need in the subquery:

SELECT Wigets1, Wigets2
FROM (SELECT WorkstationName, StartHour
      FROM InspectorApp 
      WHERE WorkstationName IN ('Wigets1', 'Wigets2')
     ) innerselect 
PIVOT(MAX(StartHour) FOR WorkstationName IN ([Wigets1], [Wigets2])) pvt;

PS. Another reason why I prefer using conditional aggregation for pivoting.

Upvotes: 2

Related Questions