1

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.

0
3
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.