Alex Alex - 17 days ago 5
SQL Question

Adding extra columns to a PIVOT join

I'm trying to join two tables so that the StaffAll table is the rows and the Checks table is the columns. Heres a summary of the data:

StaffAll
-----------------------------------
| PeronsID | FirstName | LastName |
-----------------------------------
| 10001 | James | Smith |
-----------------------------------
| 10002 | David | Collins |
-----------------------------------
| 10003 | Sarah | Brown |
-----------------------------------

Checks
------------------------------
| CheckID | PersonID | CheckTypeID | CheckDate | CheckExpiry | CheckData |
----------------------------------------------------------------------------
| 100001 | 10001 | CONR | 01/08/14 | 01/08/14 | "A string" |
----------------------------------------------------------------------------
| 100002 | 10001 | DBSC | 01/08/14 | 01/08/14 | BC5645646 |
----------------------------------------------------------------------------
| 100003 | 10002 | CONR | 01/08/14 | 01/08/14 | "Text abc" |
----------------------------------------------------------------------------
| 100004 | 10003 | DBSC | 01/08/14 | 01/08/14 | KG1215446 |
----------------------------------------------------------------------------


This is what I'm trying to produce

People_Checks
------------------------------
| PersonID | LastName | CONR | CONR_Data | DBSC | DBSC_Data |
---------------------------------------------------------------------------
| 10001 | Smith | 01/08/14 | "A String" | 01/08/14 | BC5645646 |
----------------------------------------------------------------------------
| 10002 | Collins | 01/08/14 | "Text abc" | NULL | "NULL |
---------------------------------------------------------------------------
| 10003 | Brown | NULL | NULL | 01/08/14 | KG1215446 |
---------------------------------------------------------------------------


I've managed to use PIVOT to create a column for each CheckTypeID:

WITH SCSR AS (
SELECT
S.PersonID,
S.FirstName,
S.LastName,
C.CheckDate,
C.CheckTypeID AS CheckTypeID
FROM
dbo.StaffAll S
INNER JOIN dbo.Checks C
ON S.PersonID = C.PersonID
)

SELECT *
FROM
SCSR
PIVOT (MAX([CheckDate]) FOR [CheckTypeID] IN ([APPF], [CONR], [CONS], [CPCS], [DBSC], [DISQ], [EMPH], [FAID], [IDEN], [LIST],[MEDI],[MNDQ],[OFFR],[OFFS],[POLI],[PROH],[QUAL],[REF1],[REF2],[RISK],[SRTC],[WORK])) TT;


Is there anyway to add extra PIVOT columns from the Checks table. I'd like to include the CheckData column from Checks also.

Answer

Just use conditional aggregation. In this case, with a subquery or cross apply:

select s.personid, s.lastname, c.*
from staffall s cross apply
     (select max(case when c.CheckTypeID = 'CONR' then checkdate end) as CONR,
             max(case when c.CheckTypeID = 'CONR' then checkdata end) as CONR_data,
             max(case when c.CheckTypeID = 'DBSC' then checkdate end) as DBSC,
             max(case when c.CheckTypeID = 'DBSC' then checkdata end) as DBSC_data
      from checks c
      where c.personid = s.personid
     ) c;