i have a view that currently produces the similar kind of data:
Like this i have aroun 10 properties related to a key. But i have to get these 4 rows in a single row like,
i am not sure how can i get this, i think i could use PIVOT but .. i am not sure how to use it ?? I am currently using SQL SERVER 2008 Standard.
Yes, you can use pivot. Here is the sample:
If you know all property names and they are static then just add all of them to the for property in part. Use the square brackets for each property name and they will become the column names of your crosstabbed results.
Oleg has the answer, and I have the griping... By the way, to add on slightly to Oleg's answer, you may need to do dynamic pivoting if you could have different properties for different records. A normal PIVOT statement only works if you know, in advance, the values around which you are pivoting.
The pattern in your data source is called Entity-Attribute-Value. For an entertaining (yet still informative) take, I recommend reading Phil Factor's article on the subject. I think we've all designed something like this before, but, like bad fashion, you just put it out of your mind and pretend it never happened.
You might be stuck with that EAV design (developers absolutely love the thing, at least until everything slows down because there are 1000 rows in the system), but as things grow, it scales miserably and usually needs replaced once it starts really getting used.
answered Jun 22 '11 at 12:32 PM