Alex Alex - 8 months ago 39
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 Source

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;