Getting Multiple Rows Into One

[mark1110]

I have a table that the following:

Name
Address
City
State
Zip
Phone_Type
Phone

Sample data would be:
John Smith
1234 Fifth Street
Chicago
IL
60012
Home
(312)5551212

John Smith
1234 Fifth Street
Chicago
IL
60012
Business
(312)444-1212

John Smith
1234 Fifth Street
Chicago
IL
60012
FAX
(312)333-1212

I would like to run a query that takes all the phone numbers for a person and puts it on one line like:

Name Address City State Zip Bus Num Home Num Fax Num

Is there a simple way of doing this?


Thanks,

Mark

 

[JarlH]

[tt]SELECT Name,
Address,
City,
State,
Zip,
MAX(CASE WHEN Phone_Type = 'Home' Then Phone end) AS HOME,
MAX(CASE WHEN Phone_Type = 'Business' Then Phone end) AS Business,
MAX(CASE WHEN Phone_Type = 'Fax' Then Phone end) AS Fax
FROM tablename
GROUP BY Name,
Address,
City,
State,
Zip
[/tt]

Core ANSI SQL.