How do I perform multiple joins with a dynamic pivot?

Question

Lots of posts on how to handle pivoting data (here, here, here). However, I can't find anything on performing a multiple joins on the pivoted data, specifically on the dynamic columns that are returned from the pivot.

For example, I can produce the result below using a similar query (I've made the columns not dynamic, but it could be):

select cj.JobId, cj.JobTitle, i.[100],i.[101],i.[110],i.[120]
from ContractJobs cj
inner join (
select * from
(select JobId, CustomFieldId, CustomFieldValue from ContractJobCustomFields) cjcf
PIVOT
(MAX(CustomFieldValue) FOR CustomFieldId in ([100],[101],[110],[120])) p
) i on cj.JobId = i.JobId

JobId | JobTitle  | 100   | 101   | 110   | 120
-------------------------------------------------
1     | Janitor   | 5000  | 5100  | 5101  | 5200
2     | Cook      | 5001  | 5102  | 5101  | 5500

With the supporting tables

CustomFields
CustomFieldId | CustomFieldName
-------------------------------
100           | Location
101           | Type
110           | Source
120           | Branch

CustomFieldListValues
ID   |  CustomFieldID | CustomFieldValue
-----------------------------
5000 | 100            | North Carolina
5001 | 100            | South Carolina
5100 | 101            | Retail
5102 | 101            | Commercial

I cannot find out how to perform a join on the pivot given that the columns are dynamic IDs. I'd like the final dataset to return the values, which are pulling column values from one table, and the table values from another table. What is the best way to do this?

JobId | JobTitle  | Location        | Type        | Source  | Branch
--------------------------------------------------------------------
1     | Janitor   | North Carolina  | Retail      | etc...  | etc...
2     | Cook      | South Carolina  | Commercial  | etc...  | etc...

score 0 · Answer 1 · answered Nov 08 '22 at 02:10

use a subquery to join the tables first before pivot it

select *
from   (
           select cj.JobID, cj.JobTitle, cf.CustomFieldName, cflv.CustomFieldValue
           from   ContractJobs cj
                  inner join ContractJobCustomFields cjcf 
                          on cj.JobID              = cjcf.JobID
                  inner join CustomFields cf 
                          on cjcf.CustomFieldId    = cf.ID
                  inner join CustomFieldListValues cflv 
                          on cjcf.CustomFieldValue = cflv.ID
                         and cjcf.CustomFieldId    = cflv.CustomFieldId 
       ) j
      PIVOT
      (
           MAX (CustomFieldValue) 
           FOR CustomFieldNamein ([Location], [Type], [Source], [Branch])
      ) p

How do I perform multiple joins with a dynamic pivot?

1 Answers1