2

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.

5

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.