sql-server - 从单个表中拆分列并作为多个列连接到另一个表中

Question

我想将一个表中的列拆分为另一个表中的多个列

假设我想将 FoodAttributeTable 加入 FoodTable：

美食桌

+------------+-------+
|    Food    | Price |
+------------+-------+
| Strawberry |    10 |
| Broccoli   |    25 |
+------------+-------+

食物属性表

+------------+---------------+----------------+
|    Food    | AttributeName | AttributeValue |
+------------+---------------+----------------+
| Strawberry | Vitamin       | C              |
| Strawberry | Weight        | 15g            |
| Strawberry | Color         | Red            |
| Broccoli   | Vitamin       | B              |
| Broccoli   | Weight        | 70g            |
| Broccoli   | Color         | Green          |
+------------+---------------+----------------+

表为：

美食桌

+------------+-------+---------+--------+-------+
|    Food    | Price | Vitamin | Weight | Color |
+------------+-------+---------+--------+-------+
| Strawberry |    10 | C       | 15g    | Red   |
| Broccoli   |    25 | B       | 70g    | Green |
+------------+-------+---------+--------+-------+

标签： sql-serversplitsql-server-2014multiple-columns

Answer 1

您可以使用如下所示的简单 PIVOT 查询。查看PIVOT 上的官方 MSDN 文档。

select 
Food,Price,Vitamin,Weight,Color
from
(
    select f.Food,f.Price,
     AttributeName  ,AttributeValue
    from
    FoodTable f join
    FoodAttributeTable fat on
    f.Food=fat.Food
)s
pivot
(max(AttributeValue) for AttributeName in (Vitamin,Weight,Color))
p

这里还有一个live demo