Query 2 tables one with 1 row and the other with multiple rows

Question

I have a part table:

ID,      Description
ABC-123  ABC
DEF-456  DEF

I have a User Defined Fields table that stores information in a single row.

ID,         DOCUMENTID, STRING_VAL,         DATE_VAL
UDF-000021, ABC-123,    TEXT123,            NULL
UDF-000022, ABC-123,    NULL,               6/10/2016
UDF-000023, ABC-123,    JOHN WORKING ON IT, NULL
UDF-000024, ABC-123,    NULL,               7/1/2016
UDF-000025, ABC-123,    YES,                NULL
UDF-000021, DEF-456,    TEXT123,            NULL
UDF-000022, DEF-456,    NULL,               6/10/2016

I need the information to look like this:

PART ID  DESCRIPTION    REVISION     REVISION DATE   NOTES    MARKUP DATE   IM
  -------------------------------------------------------------------------------
  ABC-123  ABC            TEXT123      6/10/2016       JOHN     7/1/2016      YES
  DEF0456  DEF            TEXT123      6/10/2016

I have looked at joins, pivot, etc. and I guess I just cant seem to wrap my head around it. Any help is greatly appreciated.

Answer 1

You can use the old fashioned PIVOT approach as you need to aggregate two different columns.

WITH Pivoted AS
(
SELECT DOCUMENTID,
       REVISION = MAX(CASE WHEN ID = 'UDF-000021' THEN STRING_VAL END),
       [REVISION DATE] = MAX(CASE WHEN ID = 'UDF-000022' THEN DATE_VAL END),
       NOTES = MAX(CASE WHEN ID = 'UDF-000023' THEN STRING_VAL END),
       [MARKUP DATE] = MAX(CASE WHEN ID = 'UDF-000024' THEN DATE_VAL END),
       IM = MAX(CASE WHEN ID = 'UDF-000025' THEN STRING_VAL END)
FROM UserDefinedFields
GROUP BY DOCUMENTID
)
SELECT p.ID,
       p.Description,
       pv.REVISION,
       pv.[REVISION DATE],
       pv.NOTES,
       pv.[MARKUP DATE],
       pv.IM
FROM   Part p
       LEFT JOIN Pivoted pv
         ON p.ID = pv.DOCUMENTID 

Online Demo