140971, Sql Server

Language: Layout:

CREATE TABLE Part
    ([ID] varchar(7), [Description] varchar(3))
;
    
INSERT INTO Part
    ([ID], [Description])
VALUES
    ('ABC-123', 'ABC'),
    ('DEF-456', 'DEF')
;

CREATE TABLE UserDefinedFields
    ([ID] varchar(10), [DOCUMENTID] varchar(7), [STRING_VAL] varchar(18), [DATE_VAL] varchar(9))
;
    
INSERT INTO UserDefinedFields
    ([ID], [DOCUMENTID], [STRING_VAL], [DATE_VAL])
VALUES
    ('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')
;
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

View schema
Execution time: 0,05 sec, rows selected: 2, rows affected: 9, absolute service time: 0,22 sec