INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?

Question

I have a table which contains attribute names with there values but each row is a attribute with its value,

ItemId	ItemAttributeName	ItemAtributeValue
405	Application	High AmpacityConnections
405	Color	Yellow
405	Environmental Conditions	Watertight
405	Material	Composite
406	Application	High AmpacityConnections
406	Color	Blue
406	Material	Brass

so I created another table with all possible Attribute names to flatten it out so that each row will contain all values for each item.

ItemId	Application	Color	Environmental Conditions	Material
405	High Ampacity Connections	Yellow	Watertight	Composite
406	High Ampacity Connections	Blue		Wood

How is it possible to take the first table and insert the data for each given itemId into the above table under corresponding columns?

I also need to consider that there is over a hundred different attribute names and not all items will have each attribute or value. I'm not really sure what direction I should go with this problem.

Answer 1

select itemid, 
       MAX(case 
              when itemattributename ='Application' then itemattributevalue 
              ELSE NULL END),
       MAX(case 
              when itemattributename ='Color' then itemattributevalue 
              ELSE NULL END)Color,
       ISNULL(MAX(case 
              when itemattributename ='Environmental Conditions' then 
                   itemattributevalue 
              ELSE NULL END),'')[Environmental Conditions],
       MAX(case 
              when itemattributename ='Material' then itemattributevalue 
             ELSE  NULL  END)Material
  from item
 group by itemid

The above solution works only if you have a definite number of attributes, if there in N number of attributes you should use dynamic pivot for this

Answer 2

To complement the answer already provided by Biju jose, here is one way to do it with dynamic SQL. If you add new rows to the dbo.eav table in my example, they automatically translate into new columns in the output.

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.eav;
GO

CREATE TABLE dbo.eav
(
      [ItemId]              int             NOT NULL
    , [ItemAttributeName]   varchar(50)     NOT NULL
    , [ItemAttributeValue]  varchar(50)     NOT NULL
);

INSERT INTO dbo.eav ([ItemId], [ItemAttributeName], [ItemAttributeValue])
VALUES
      (405, 'Application', 'High Ampacity Connections')
    , (405, 'Color', 'Yellow')
    , (405, 'Environmental Conditions', 'Watertight')
    , (405, 'Material', 'Composite')
    , (406, 'Application', 'High Ampacity Connections')
    , (406, 'Color', 'Blue')
    , (406, 'Material', 'Brass')
    , (407, 'Size', 'Small')
    , (407, 'Application', 'Stapler');

DECLARE @dynamic_sql nvarchar(max) = N'SELECT src.ItemId, ';

DROP TABLE IF EXISTS #names;

SELECT e.ItemAttributeName
INTO #names
FROM dbo.eav e
GROUP BY e.ItemAttributeName
ORDER BY e.ItemAttributeName;

SET @dynamic_sql = @dynamic_sql + (
    SELECT STRING_AGG(QUOTENAME(e.ItemAttributeName) + N' = MAX(' + QUOTENAME(e.ItemAttributeName) + N')', N', ')
    FROM #names e
    );

SET @dynamic_sql = @dynamic_sql + N' FROM src GROUP BY src.[ItemId] ORDER BY src.[ItemId];'

SET @dynamic_sql = (
    SELECT N'
;WITH src AS 
(
    SELECT e.ItemId
        , ' + STRING_AGG(QUOTENAME(e.ItemAttributeName) + N' = CASE WHEN e.ItemAttributeName = ''' + e.ItemAttributeName + N''' THEN e.ItemAttributeValue ELSE NULL END', CHAR(13) + CHAR(10) + N'        , ')
    FROM #names e 
    ) 
    + '
    FROM dbo.eav e
)
' 
    + @dynamic_sql + N'

';

PRINT @dynamic_sql;
EXEC sys.sp_executesql @dynamic_sql;

Output from the above looks like:

ItemId	Application	Color	Environmental Conditions	Material	Size
405	High Ampacity Connections	Yellow	Watertight	Composite	NULL
406	High Ampacity Connections	Blue	NULL	Brass	NULL
407	Stapler	NULL	NULL	NULL	Small

As you can see, my output includes ItemId 407, with an attribute of Size "Small".

Of course, as the unique number of ItemAttributeName values goes up, the wider the output becomes. It seems unlikely that you'd really want to do that. More likely would be to select a specific ItemId and only show the applicable columns.

The below example builds on the above code to show how you could dynamically select a specific ItemId (in this case 407), to show only the relevant columns:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.eav;
GO

CREATE TABLE dbo.eav
(
      [ItemId]              int             NOT NULL
    , [ItemAttributeName]   varchar(50)     NOT NULL
    , [ItemAttributeValue]  varchar(50)     NOT NULL
);

INSERT INTO dbo.eav ([ItemId], [ItemAttributeName], [ItemAttributeValue])
VALUES
      (405, 'Application', 'High Ampacity Connections')
    , (405, 'Color', 'Yellow')
    , (405, 'Environmental Conditions', 'Watertight')
    , (405, 'Material', 'Composite')
    , (406, 'Application', 'High Ampacity Connections')
    , (406, 'Color', 'Blue')
    , (406, 'Material', 'Brass')
    , (407, 'Size', 'Small')
    , (407, 'Application', 'Stapler');


DECLARE @ItemIdFilter int = 407;
DECLARE @dynamic_sql nvarchar(max) = N'SELECT src.ItemId, ';

DROP TABLE IF EXISTS #names;

SELECT e.ItemAttributeName
INTO #names
FROM dbo.eav e
WHERE e.ItemId = @ItemIdFilter
GROUP BY e.ItemAttributeName
ORDER BY e.ItemAttributeName;

SET @dynamic_sql = @dynamic_sql + (
    SELECT STRING_AGG(QUOTENAME(e.ItemAttributeName) + N' = MAX(' + QUOTENAME(e.ItemAttributeName) + N')', N', ')
    FROM #names e
    );

SET @dynamic_sql = @dynamic_sql + N' FROM src GROUP BY src.[ItemId] ORDER BY src.[ItemId];'

SET @dynamic_sql = (
    SELECT N'
;WITH src AS 
(
    SELECT e.ItemId
        , ' + STRING_AGG(QUOTENAME(e.ItemAttributeName) + N' = CASE WHEN e.ItemAttributeName = ''' + e.ItemAttributeName + N''' THEN e.ItemAttributeValue ELSE NULL END', CHAR(13) + CHAR(10) + N'        , ')
    FROM #names e 
    ) 
    + '
    FROM dbo.eav e
    WHERE e.ItemId = ' + CONVERT(nvarchar(20), @ItemIdFilter) + N'
)
' 
    + @dynamic_sql + N'

';

PRINT @dynamic_sql;
EXEC sys.sp_executesql @dynamic_sql;

The output from that looks like:

ItemId	Application	Size
407	Stapler	Small

The dynamically generated T-SQL for the above output looks like:

;WITH src AS 
(
    SELECT e.ItemId
        , [Application] = CASE WHEN e.ItemAttributeName = 'Application' THEN e.ItemAttributeValue ELSE NULL END
        , [Size] = CASE WHEN e.ItemAttributeName = 'Size' THEN e.ItemAttributeValue ELSE NULL END
    FROM dbo.eav e
    WHERE e.ItemId = 407
)
SELECT src.ItemId, [Application] = MAX([Application]), [Size] = MAX([Size]) FROM src GROUP BY src.[ItemId] ORDER BY src.[ItemId];