2

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?

intrigued_66
  • 16,082
  • 51
  • 118
  • 189
  • Are your final columns fixed? Or could new keys appear in the data that you then need to appear as new columns in your results? *(If it's fixed, look at PIVOT and UNPIVOT, if it's dynamic you'll need to write Dynamic SQL to adapt the PIVOT to encorporate the new keys.)* – MatBailie Jun 27 '12 at 11:24

1 Answers1

6

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

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • 2
    How would this work if _value_ were not a numeric value but a textfield? – Calyx Aug 05 '19 at 20:34
  • 1
    @Calyx - In place of `SUM()`, you can use `MAX()` as a valid aggregate for strings, or in newer versions `STRING_AGG()`, depending on intent. – MatBailie Oct 06 '21 at 10:27