Transforming rows into columns

[jpasqua]

Hi All,

I'm not sure how best to describe what I'm really looking to do, so
I'll just give you my example and hopefully someone can help.

I have a table that looks similar to this:

Primary Key Field Type Field Value
--------------------------------------
1234 FName Sally
1234 LName Smith
1234 Phone [phone]
7777 FName John
7777 LName Riley
4444 LName Johnson
4444 Phone [phone]
etc...

Which I want to move into a format that looks like this:

Primary Key FName LName Phone
-----------------------------------------
1234 Sally Smith 555-555-5555
7777 John Riley
4444 Johnson 222-234-5555

What is the most efficient way to do this? I'd rather not create a
separate query for each of the field types (there are more than 3 in my
actual situation).

Any help you can offer would be appreciated!

Jeff

[David Portas]

INSERT INTO Contacts (primary_key, fname, lname, phone)
SELECT primary_key,
MAX(CASE WHEN field_type = 'fname' THEN field_value END),
MAX(CASE WHEN field_type = 'lname' THEN field_value END),
MAX(CASE WHEN field_type = 'phone' THEN field_value END)
FROM foo
GROUP BY primary_key

--
David Portas
SQL Server MVP
--

[jpasqua]

Thanks, worked like a charm!