x

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.

more ▼

asked Jun 22 '11 at 11:19 AM in Default

Katie 1 gravatar image

Katie 1
1.4k 129 163 202

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jun 22 '11 at 11:36 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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][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.

[3]: http://www.simple-talk.com/community/blogs/philfactor/archive/2008/05/29/56525.aspx
more ▼

answered Jun 22 '11 at 12:32 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x362
x235

asked: Jun 22 '11 at 11:19 AM

Seen: 2047 times

Last Updated: Jun 22 '11 at 11:43 AM