将行转置为列并连接表格

Question

你好，我有两个表格 Attribute 和 Instance。

Attribute 表格

id  |    key_info   |   value_info
2   |     amount    |    1009
2   |   currency    |     USD
2   |     Level     |    Level 5
3   |    amount     |     2017 
3   |   currency    |     CAD

实例表格

id   |  status
2    | Pending 
3    | Approved

I want to join two tables like this-

New table

id | amount |  currrency | level   | status
2  |  1001  |     USD    | Level 5 | Pending
3  |   2017 |     CAD    |         | Approved

除了id之外，属性和实例中的所有字段都是可选的。

- Sujithk109

---

这是针对哪个关系型数据库管理系统？MySQL？SQL Server？每个平台实现数据透视功能的方式都不同。 - Michael Fredrickson

这是针对SQL Server 2008的。上面的查询可行。谢谢。 - Sujithk109

Answer 1

select
  a.id,
  max(case when a.key_info = 'amount' then a.value_info end) as amount,
  max(case when a.key_info = 'currency' then a.value_info end) as currency,
  max(case when a.key_info = 'level' then a.value_info end) as level,
  i.status
from
  attribute a
  join instance i on a.id = i.id
group by
  a.id,
  i.status

Sql Fiddle

- Michael Fredrickson

Answer 2

从SQL Server 2005开始，PIVOT函数可以将行转换为列：

select id, 
  amount, 
  currency, 
  level,
  status
from 
(
  select i.id, i.status,
    a.key_info,
    a.value_info
  from instance i
  inner join attribute a
    on i.id = a.id
) src
pivot
(
  max(value_info)
  for key_info in (amount, currency, level)
) piv

请查看带演示的SQL Fiddle.

如果你有未知数量的key_values，可以使用动态SQL：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(key_info) 
                    from Attribute
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ', status from 
             (
                select i.id, i.status,
                  a.key_info,
                  a.value_info
                from instance i
                inner join attribute a
                  on i.id = a.id
            ) x
            pivot 
            (
                max(value_info)
                for key_info in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL Fiddle。两者都产生以下结果：

| ID | AMOUNT | CURRENCY |   LEVEL |   STATUS |
-----------------------------------------------
|  2 |   1009 |      USD | Level 5 |  Pending |
|  3 |   2017 |      CAD |  (null) | Approved |

- Taryn