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.