row to column data

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

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.