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