SQL Condensing multiple rows into one-CodePudding

I got the following SQL statement which results in the first table below. My goal is to have an output like seen in the bottom table. How do I achieve that?

SELECT tabDynValues.Dynamic_Field_ID,
       tabDynValues.Record_ID,
       CASE Dynamic_Field_ID
        WHEN 755 THEN tabDynValues.Value_Char
       END Filiale,
       CASE Dynamic_Field_ID
        WHEN 756 THEN tabDynValues.Value_Double
       END AD,
       CASE Dynamic_Field_ID
        WHEN 757 THEN tabDynValues.Value_Double
       END Vertrieb,
       CASE Dynamic_Field_ID
        WHEN 758 THEN tabDynValues.Value_Double
       END Expansion
  FROM CI_AM_Dynamic_Field_Value tabDynValues
 WHERE tabDynValues.Activity_ID = 100612
   AND tabDynValues.Dynamic_Field_ID IN (SELECT Dynamic_Field_ID
                                           FROM CI_AM_Dynamic_Field
                                          WHERE Dynamic_Field_Group_ID = 238)
ORDER BY tabDynValues.Record_ID, tabDynValues.Dynamic_Field_ID

Dynamic_Field_ID	Record_ID	Filiale	AD	Vertrieb	Expansion
755	1	1111 Mitte	NULL	NULL	NULL
756	1	NULL	123	NULL	NULL
757	1	NULL	NULL	56	NULL
758	1	NULL	NULL	NULL	45
755	2	2222 Ost	NULL	NULL	NULL
756	2	NULL	NULL	120	NULL
755	3	3333 West	NULL	NULL	NULL
756	3	NULL	123	NULL	NULL
757	3	NULL	NULL	456	NULL
758	3	NULL	NULL	NULL	789

Record_ID	Filiale	AD	Vertrieb	Expansion
1	1111 Mitte	123	56	45
2	2222 Ost	120	NULL	NULL
3	3333 West	123	456	789

CodePudding user response：

Just add a GROUP BY and wrap your case expressions inside MAX function:

SELECT
    tabDynValues.Record_ID,
    MAX(CASE Dynamic_Field_ID WHEN 755 THEN tabDynValues.Value_Char END) Filiale,
    MAX(CASE Dynamic_Field_ID WHEN 756 THEN tabDynValues.Value_Double END) AD,
    MAX(CASE Dynamic_Field_ID WHEN 757 THEN tabDynValues.Value_Double END) Vertrieb,
    MAX(CASE Dynamic_Field_ID WHEN 758 THEN tabDynValues.Value_Double END) Expansion
FROM CI_AM_Dynamic_Field_Value tabDynValues
WHERE tabDynValues.Activity_ID = 100612 AND tabDynValues.Dynamic_Field_ID IN (
    SELECT Dynamic_Field_ID
    FROM CI_AM_Dynamic_Field
    WHERE Dynamic_Field_Group_ID = 238
)
GROUP BY tabDynValues.Record_ID
ORDER BY tabDynValues.Record_ID