SEO

#1 (**permalink**) 02-29-2008, 06:32 AM

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 555-555-5555
7777 FName John
7777 LName Riley
4444 LName Johnson
4444 Phone 222-234-5555
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

#2 (**permalink**) 02-29-2008, 06:32 AM

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

#3 (**permalink**) 02-29-2008, 06:33 AM

Thanks, worked like a charm!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Transforming rows into columns