CODEWITHSUNDEEP
sql-servert-sql
MPJ567
MPJ567

Reputation: 553

Add Row Values as Columns to Existing Table

It seems like the answer for me lies somewhere within PIVOT and dynamic SQL but I can't put the pieces together. I have the following table.

FieldId InternalFieldName  FieldLabel
1       RepairableSpare_1  CField0
6       Equipment_6        CField1
7       Equipment_7        CField2
8       Equipment_8        CField3
9       Equipment_9        CField4

I need to add the values in the FieldLabel column, as their own columns in another table.

End goal would be:

OtherTableID OtherTableColumnA CField0 CField1 CField2 CField3 CField4
ID1          ColumnValue       VALUE   VALUE   VALUE   VALUE   VALUE

I have a third table that I can use to populate the VALUES in those CField columns, I just can't figure out how to get them as columns in the OtherTable.

EDIT

I've attached an image of the tables I'm working with and text information below. enter image description here

CustomField Table

FieldId InternalFieldName FieldLabel
6       Equipment_6       CustomField1
7       Equipment_7       CustomField2
8       Equipment_8       CustomField3
9       Equipment_9       CustomField4

Equipment Table

EquipmentId EquipmentNo
7362        ..12
8696        ..12_COPY2

Desired Table

EquipmentId EquipmentNo CustomField1 CustomField2 CustomField3 CustomField4
7362        ..12
8696        ..12_COPY2

Important note, the number of custom fields is not static.

EDIT2

I thought I could figure it out from a certain point, but it looks like that was not the (big shout out to D-shih for sticking with me) case. Below I've included the other table I'll need to work with that I thought I could figure out myself.

I have 3 tables:

CustomField Table

FieldId InternalFieldName FieldLabel
6       Equipment_6       CustomField1
7       Equipment_7       CustomField2
8       Equipment_8       CustomField3
9       Equipment_9       CustomField4

CustomFieldRelationshipTable

FieldValueId FieldId EquipmentId FieldValue
66           6       7431        True
67           7       7431        1900-01-01
68           8       7431        1900-01-01
69           9       7431        NULL

Equipment Table

EquipmentId EquipmentNo
7431         ..12

Desired Table

EquipmentId EquipmentNo CustomField1 CustomField2 CustomField3 CustomField4
7431        ..12        True         1900-01-01   1900-01-01   NULL

Upvotes: 1

Views: 116

Answers (1)

D-Shih
D-Shih

Reputation: 46219

I think you can try to use CROSS APPLY with condition aggregate function.

and execute by dynamic SQL.

CREATE TABLE CustomField(
  FieldId INT,
  InternalFieldName VARCHAR(50),
  FieldLabel VARCHAR(50)
);


INSERT INTO  CustomField VALUES (6,'Equipment_6','CustomField1');
INSERT INTO  CustomField VALUES (7,'Equipment_7','CustomField2');
INSERT INTO  CustomField VALUES (8,'Equipment_8','CustomField3');
INSERT INTO  CustomField VALUES (9,'Equipment_9','CustomField4');

CREATE TABLE Equipment(
  EquipmentId INT,
  EquipmentNo VARCHAR(50)
);


INSERT INTO Equipment VALUES (7362,'..12');
INSERT INTO Equipment VALUES (8696,'..12_COPY2');

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);


SET @cols = STUFF((SELECT distinct ',max(case when FieldLabel = ''' + FieldLabel + ''' then InternalFieldName end) ' +    QUOTENAME(FieldLabel) 
            FROM CustomField
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


set @query = 'SELECT EquipmentId,EquipmentNo,'+@cols+'
FROM 
(
   SELECT * 
   FROM CustomField
) ta CROSS APPLY 
(
   SELECT * 
   FROM Equipment
) tb
GROUP BY  EquipmentId,
       EquipmentNo
'


EXECUTE(@query)

sqlfiddle

if you want to let CustomField be the empty string you just modify the case when

SET @cols = STUFF((SELECT distinct ',max(case when FieldLabel = '''' then InternalFieldName end) ' +    QUOTENAME(FieldLabel) 
            FROM CustomField
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

sqlfiddle

Upvotes: 1

Related Questions