470,855 Members | 1,158 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,855 developers. It's quick & easy.

Transforming rows into columns

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

Jul 23 '05 #1
2 1259
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
--

Jul 23 '05 #2
Thanks, worked like a charm!

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jim Heavey | last post: by
68 posts views Thread by Martin Joergensen | last post: by
5 posts views Thread by james_027 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.