主页 / dba / 问题 / 295372
Accepted
zerodoc
Asked: 2021-07-08 09:41:20 +0800 CST

根据表 A 在其行中的值将表 A 中的值插入或更新到表 B 的列中？

  • 772

我有一个表，其中包含具有值的属性名称，但每一行都是具有其值的属性，

项目 ID 项目属性名称 项目属性值
405 应用 高载流量连接
405 颜色 黄色
405 环境条件 防水
405 材料 合成的
406 应用 高载流量连接
406 颜色 蓝色的
406 材料 黄铜

所以我创建了另一个表，其中包含所有可能的属性名称以将其展平，以便每一行都包含每个项目的所有值。

项目 ID 应用 颜色 环境条件 材料
405 高载流量连接 黄色 防水 合成的
406 高载流量连接 蓝色的 木头

如何获取第一个表并将每个给定 itemId 的数据插入到相应列下的上表中？

我还需要考虑有超过一百个不同的属性名称，并不是所有的项目都会有每个属性或值。我不确定我应该向哪个方向解决这个问题。

sql-server t-sql

2 个回答

  1. Best Answer
    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

    上述解决方案仅在您有一定数量的属性时才有效，如果有 N 个属性，您应该为此使用动态枢轴

    • 3

  2. 为了补充Biju jose已经提供的答案，这里有一种使用动态 SQL 的方法。如果您dbo.eav在我的示例中向表中添加新行，它们会自动转换为输出中的新列。

    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;

    上面的输出如下所示：

    项目 ID 应用 颜色 环境条件 材料 尺寸
    405 高载流量连接 黄色 防水 合成的 无效的
    406 高载流量连接 蓝色的 无效的 黄铜 无效的
    407 订书机 无效的 无效的 无效的 小的

    如您所见，我的输出包括 ItemId 407，其属性为“Small”。

    当然，随着唯一ItemAttributeName值数量的增加，输出变得越宽。您似乎不太可能真的想这样做。更有可能选择特定的 ItemId 并仅显示适用的列。

    下面的示例建立在上面的代码之上，展示了如何动态选择特定的ItemId（在本例中为 407），以仅显示相关的列：

    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;

    其输出如下所示：

    项目 ID 应用 尺寸
    407 订书机 小的

    为上述输出动态生成的 T-SQL 如下所示：

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

相关问题