×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Getting Multiple Rows Into One

Getting Multiple Rows Into One

Getting Multiple Rows Into One

(OP)
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
 

RE: Getting Multiple Rows Into One

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


Core ANSI SQL.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close