question

Katie 1 avatar image
Katie 1 asked

row to column data

Hi, i have a view that currently produces the similar kind of data: key name property value 100 John Address 25 Bellvue 70656 100 John Height 5.2 100 John Occupation Attorney 100 John MaritalStatus Married Like this i have aroun 10 properties related to a key. But i have to get these 4 rows in a single row like, Key Name Address Height Occupation MaritalStatus 100 John 25 Bellvue 70656 5.2 Attorney Married 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. Thanks.
sql-server-2008querydatabase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
Yes, you can use pivot. Here is the sample: -- create sample table create table #test ( [Key] int, name varchar(10), property varchar(20), value varchar(20) ); go -- insert sample data insert into #test select 100, 'Mary', 'Address', '25 Bellvue 70656' union all select 100, 'Mary', 'Height', '5.2' union all select 100, 'Mary', 'Occupation', 'Attorney' union all select 100, 'Mary', 'MaritalStatus', 'Married' union all select 200, 'John', 'Address', '1234 Main 77010' union all select 200, 'John', 'Height', '5.9' union all select 200, 'John', 'Occupation', 'DBA' union all select 200, 'John', 'MaritalStatus', 'Married'; go -- final query select * from ( select [Key], Name, Property, Value from #test ) src pivot ( max(Value) for property in ([Address], [Height], [Occupation], [MaritalStatus]) ) pt; -- results Key Name Address Height Occupation MaritalStatus --- ---- ---------------- ------ ---------- ------------- 100 Mary 25 Bellvue 70656 5.2 Attorney Married 200 John 1234 Main 77010 5.9 DBA Married 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
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][1] 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][2]. For an entertaining (yet still informative) take, I recommend reading [Phil Factor's article on the subject][3]. 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. [1]: http://pratchev.blogspot.com/2008/12/dynamic-pivoting.html [2]: http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html [3]: http://www.simple-talk.com/community/blogs/philfactor/archive/2008/05/29/56525.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.