[SOLVED] SQL Server dynamic Pivot to return one row for each category

SQL Server dynamic Pivot to return one row for each category

My database is SQL Server 2016. We receive source data in an Excel workbook. The worksheet that contains the source data has four columns. The EFFDATE is common for all load types and Trans. That is why the columns are null for the EFFDATE.

FieldName	FieldData	LoadType	Trans
EFFDATE	7/1/2021	NULL	NULL
FACILITYNUMBER	00109	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.NetworkCode_PHA_999	999	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.AccomCode_3	3	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.LAB_PHA_ReimbType	L	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.FFPHA_OPPassThru_LabRad		V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.FFPHA_OPLCC_LabRad	0.6555	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.Category	LAB	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
V.Category	RAD	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH

During processing, we have an application that connects to the Excel workbook via an OLEDB connection. The raw data is loaded to a database table and a trigger fires to add foreign keys for the field name and the load type. The data in the table looks as follows

SeqNo	FileKey	FieldKey	FieldName	FieldData	LoadTypeKey	LoadType	Trans
21	1002	1	EFFDATE	7/1/2021	0	NULL	NULL
97	1002	3	FACILITYNUMBER	00109	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
98	1002	29	V.NetworkCode_PHA_999	999	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
99	1002	6	V.AccomCode_3	3	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
100	1002	27	V.LAB_PHA_ReimbType	L	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
101	1002	17	V.FFPHA_OPPassThru_LabRad		16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
102	1002	15	V.FFPHA_OPLCC_LabRad	0.6555	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
103	1002	26	V.Category	LAB	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH
104	1002	32	V.Category	RAD	16	V_HFR_Input_OPRate_LabRad_PHA_R	BPRH

I have a stored procedure to dynamically pivot the rows of source data to a single row with the field names as the column headings, and the field data as the value. The problem I have with this particular set of data is there a two categories, "LAB" and "RAD". Because the SQL pivot requires an aggregate function, I only get one row returned. If I use PIVOT (MAX(FieldData) FOR FieldName, I get a row for "RAD". If I use PIVOT (MIN(FieldData) FOR FieldName, I get a row for "LAB".

How do I formulate the query to get one row each for LAB, and RAD? The desired result should match the following.

FileKey	SeqNo	LoadTypeKey	FacilityCode	CategoryCode	Network	AccomCode	EffectDate	ReimburseType	PassThruPct	LowerCostChrg	RatioCostChrg
1002	97	16	00109	LAB	999	3	7/1/2021	L		0.6555	NULL
1002	97	16	00109	RAD	999	3	7/1/2021	L		0.6555	NULL

Solution

It's a pretty weird pivot, but you could use windowed conditional aggregation for this:

SELECT *
FROM (
    SELECT
      FileKey,
      SeqNo,
      LoadTypeKey,
      FacilityCode = MAX(CASE WHEN FieldName = 'FACILITYNUMBER' THEN FieldData END) OVER (PARTITION BY FileKey),
      CategoryCode = FieldData,
      Network = MAX(CASE WHEN FieldName = 'V.NetworkCode_PHA_999' THEN FieldData END) OVER (PARTITION BY FileKey),
      AccomCode = MAX(CASE WHEN FieldName = 'V.AccomCode_3' THEN FieldData END) OVER (PARTITION BY FileKey),
      EffectDate = MAX(CASE WHEN FieldName = 'EFFDATE' THEN FieldData END) OVER (PARTITION BY FileKey),
      ReimburseType = MAX(CASE WHEN FieldName = 'V.LAB_PHA_ReimbType' THEN FieldData END) OVER (PARTITION BY FileKey),
      PassThruPct = MAX(CASE WHEN FieldName = 'V.FFPHA_OPPassThru_LabRad' THEN FieldData END) OVER (PARTITION BY FileKey),
      LowerCostChrg = MAX(CASE WHEN FieldName = 'V.FFPHA_OPLCC_LabRad' THEN FieldData END) OVER (PARTITION BY FileKey)
    FROM YourTable t
) t
WHERE FieldName = 'V.Category';