Transposing a Key Value pair SQL table

Question

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?

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
– MatBailie, Commented Jun 27, 2012 at 11:24

MatBailie · Accepted Answer · 2012-06-27 11:40:32Z

7

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

answered Jun 27, 2012 at 11:40

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Calyx Over a year ago

How would this work if value were not a numeric value but a textfield?

MatBailie Over a year ago

@Calyx - In place of SUM(), you can use MAX() as a valid aggregate for strings, or in newer versions STRING_AGG(), depending on intent.

Collectives™ on Stack Overflow

Transposing a Key Value pair SQL table

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related