使用一个键组合数据库条目以生成每个键一个条目的表

Question

我拥有什么

我有一个sql数据库，其中有几个表通过整数键相互关联。下面是我的3个表、列名以及每个表的一些示例数据。请记住，我只是想给出一个想法，它不是从数据库直接复制/粘贴的（因此格式化是为了传达这一点，而不是sql数据库解析器可读的）

表1

ItemTable
itemID,itemName, fruitOrVeggie, Color
1, Apple, Fruit, Red
2, Orange, Fruit, Orange
3, Carrot, Vegetable, Orange

表2

AttributeTypesTable
attributeID,attributeName
1, Price
2, Weight
3, Diameter

表3

ItemAttributesTable
itemID,attributeID,attributeValue
1, 1, .75
1, 2, .5
1, 3, .7
2, 1, .9
2, 3, .7
3, 1, .3
3, 2, .5

请注意，ItemAttributesTable中的每个itemID都有多个条目——这是我试图整合到新表中的部分。

我想要什么

从这三个表中，我想创建一个这样的新表。

NewTable
itemID,itemName,fruitOrVeggie,Color,Price,Weight,Diameter
1, Apple, Fruit, Red, .75, .5, .7
2, Orange, Fruit, Orange, .9, , .7
3, Carrot, Vegetable, Orange, .3, .5, 

在这个NewTable中，itemID是一个唯一的键，因此每个itemID只有一个条目——这就是目标。请注意每个attributeName现在是这个新表中的一列，以及ItemAttributesTable中的相应数据现在是如何在这里列出的，每个条目ID只有一个条目（如果ItemAttributesTables没有该条目ID的attributeID条目，则将字段留空）。我不想在列名中硬编码，因为我的实际数据大约有十几列，我希望这个查询足够通用，即使attributeName发生变化，我也可以添加或删除其中的一些列，等等。

如何到达那里

我主要关注这类复杂查询所涉及的sql，尽管使用某种shell来实际创建这个新表可能会很好。例如，一个查询，然后是运行该查询以创建ItemAttributesTable的Python脚本。

关键部分是如何根据另一个表（在本例中为attributeName）中的条目在新表中创建列，然后如何正确地从多个表中提取数据以填充此新表。

Answer 1

在SQLServer2005+中，可以使用PIVOT运算符旋转表值表达式。SELECT…INTO创建一个新表，并将查询得到的行插入其中

IF OBJECT_ID('NewTable') IS NOT NULL DROP TABLE NewTable
SELECT ItemID, ItemName, FruitOrVeggie, Color, Price, Weight, Diameter
INTO NewTable
FROM      
(      
 SELECT t.ItemID, t.ItemName, t.FruitOrVeggie, Color, attributeName, attributeValue
 FROM ItemTable t JOIN ItemAttributesTable at ON t.ItemID = at.ItemID
                  JOIN AttributeTypesTable tt ON at.attributeID = tt.attributeID
) x
PIVOT
(
 MAX(attributeValue) FOR attributeName IN ([Price], [Weight], [Diameter])
 ) p
SELECT *
FROM NewTable

SQLFiddle上的演示

或

如果要转换的列（attributeName）数量未知，则可以使用动态PIVOT。

DECLARE @cols AS nvarchar(max),
        @query AS nvarchar(max)
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(attributeName)
                      FROM AttributeTypesTable
                      FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
IF OBJECT_ID('NewTable') IS NOT NULL DROP TABLE NewTable                      
SET @query = 'SELECT ItemID, ItemName, FruitOrVeggie, Color, ' + @cols + 
             'INTO NewTable FROM 
             (
              SELECT t.ItemID, t.ItemName, t.FruitOrVeggie, Color, attributeName, attributeValue
              FROM ItemTable t JOIN ItemAttributesTable at ON t.ItemID = at.ItemID
                               JOIN AttributeTypesTable tt ON at.attributeID = tt.attributeID
              ) x
              PIVOT
              (
               MAX(attributeValue) FOR attributeName IN (' + @cols + ')
               ) p '
EXEC(@query)
SELECT *
FROM NewTable 

SQLFiddle

Answer 2

我会尝试这样的东西：

INSERT INTO NEW TABLE (itemID, itemName, fruitOrVeggie, Color, Price, Weight, Diameter)
SELECT IT.itemID, IT.itemName, IT.fruitOrVeggie, IT.Color, Price.attributeValue, Weight.attributeValue, Diameter.attributeValue
FROM ItemTable IT
LEFT OUTER JOIN ( SELECT itemId, attributeValue
        FROM ItemAttributesTable
        WHERE attributeID = 1
    ) AS Price
ON Price.itemID = IT.itemID
LEFT OUTER JOIN ( SELECT itemId, attributeValue
        FROM ItemAttributesTable
        WHERE attributeID = 2
    ) AS Weight
ON Weight.itemID = IT.itemID
LEFT OUTER JOIN ( SELECT itemId, attributeValue
        FROM ItemAttributesTable
        WHERE attributeID = 3
    ) AS Diameter
ON Diameter.itemID = IT.itemID

插入语法可能因所使用的特定SQL实现而异。我建议在运行插入之前，先尝试Select部分，看看返回的行是否与您希望newTable中的行相匹配。