CODEWITHSUNDEEP
sqlsql-serversql-server-2008
intrigued_66
intrigued_66

Reputation: 17258

Transposing a Key Value pair SQL table

I have a table which consists of a few columns, including a Key Value pairing. So imagine:

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

I need to get this into the format:

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

i.e transposing the cells- how would I do this in SQL?

Upvotes: 2

Views: 8798

Answers (1)

MatBailie
MatBailie

Reputation: 86765

Something like this?

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

Upvotes: 7

Related Questions