# 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,

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.

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

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

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