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

All we need is an easy explanation of the problem, so here it is.

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

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.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 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:

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:

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];

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0