sql table pivot

Question

我的表当前包含:

blog | id | attribute | value
p    | 1  |  pid      | abc1
p    | 1  |  date     | abc2
p    | 1  |  title    | abc3
p    | 2  |  id       | abc1
p    | 2  |  date     | abc2
p    | 2  |  title    | abc3
p    | 3  |  id       | abc1
p    | 3  |  date     | abc2
p    | 3  |  title    | abc3

我需要把这个改成:

blog | id | postid | date | title
p    | 1  | abc1   | abc2 | abc3
p    | 2  | abc1   | abc2 | abc3
p    | 3  | abc1   | abc2 | abc3

在sql中做到这一点的最好方法是什么?

Answer 1

您没有指定您正在使用的RDBMS，但这将在所有版本中工作:

select blog,
  id,
  max(case when attribute = 'pid' then value end) postid,
  max(case when attribute = 'date' then value end) date,
  max(case when attribute = 'title' then value end) title
from yourtable
group by blog, id

参见SQL Fiddle with Demo

如果您正在使用具有PIVOT函数的数据库，那么您的查询将是这样的:

select blog, id, pid as postid, date, title
from 
(
  select blog, id, attribute, value
  from yourtable
) src
pivot
(
  max(value)
  for attribute in (pid, date, title)
) piv

参见SQL Fiddle with Demo

两者的结果将是:

| BLOG | ID | POSTID | DATE | TITLE |
-------------------------------------
|    p |  1 |   abc1 | abc2 |  abc3 |
|    p |  2 |   abc1 | abc2 |  abc3 |
|    p |  3 |   abc1 | abc2 |  abc3 |