SQL Server PIVOT on key-value table

Posted by Zenox on Stack Overflow See other posts from Stack Overflow or by Zenox
Published on 2010-12-29T16:49:55Z Indexed on 2010/12/29 16:54 UTC
Read the original article Hit count: 142

Filed under:
|

I have a table that has attributes based on a key-value. Example:

CREATE TABLE ObjectAttributes
(
  int objectId, key nvarchar(64), value nvarchar(512)
)

When I select from this I get:

objectId  key      value
----------------------------
1         Key 1    Value 1
1         Key 2    Value 2

I was wondering if I could use the PIVOT syntax to turn this into:

objectId  Key 1     Key 2
---------------------------
1         Value 1   Value 2

I know all of my tables will have the same keys. (Unfortunately I cannot easily change the table structure. This is what is leading me to attempt using PIVOTS).

The big issue here though is that pivots require an aggression function to be used. Is there a way to avert this? Am I completely wrong attempting this? Or is there a better solution?

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about pivot