移调键值对SQL表(Transposing a Key Value pair SQL table)

我有一个由几列，其中包括一个键值配对表。 所以，想象一下：

BookID, Key, Value
MyBook, Price, 19.99
MyBook, ISBN, 987878495793
MyBook, Pages, 354
MyBook2, ...., ....
MyBook2, ...., ....

我需要进入这个格式：

BookID, Price, ISBN, Pages
MyBook, 19.99, 987878495793, 354
MyBook2,.....,.............,....

即调换细胞 - 我将如何在SQL这样做呢？

Answer 1:

像这样的事情？

SELECT
  BookID, Price, ISBN, Pages
FROM
(
  SELECT BookID, Key, Value FROM yourTable
)
  AS SourceTable
PIVOT
(
  SUM(Value) FOR Key IN (Price, ISBN, Pages)
)
  AS PivotTable
ORDER BY
  BookID

http://technet.microsoft.com/en-us/library/ms177410(SQL.105).aspx

文章来源: Transposing a Key Value pair SQL table