Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 5th, 2008, 03:53 PM
Authorized User
 
Join Date: Jul 2006
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Statement

Hi There

I have a table that contains contact No's (telephone) for dog owners.
A dog owner can have more than one contact no (obviousley). The table fields are:

contact_dialler_1, contact_dialer_2 and contact_dialer_3

Contact_dialer_1 = "D","C","A" for day, cell and After hours

I would like to select all contact data and have one (1) line for each contact by putting the contact no's in there own fields eg:

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567

Can any one help with how to select the Contact_dialer fields so that there is only one line per contact and not two or three depending on how many telephone no's they have.

Thank

Greg

 
Old May 6th, 2008, 11:25 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

From what you have posted so far, it is not clear what your source data structure is.

From the looks of it you already have three fields for each person. However, from the sound of your question you have many records for each person and you want to wrap them up into a single line. Which is it?

It would help us to see your table schema for the source data.

-Peter
compiledthoughts.com
 
Old May 15th, 2008, 05:45 PM
Authorized User
 
Join Date: Jul 2006
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Peter,

Sorry to reply so late but I've been away on course and a bit of leave.
There are three tel no's for each contact but sometimes there are more than one no for a type - sy day_phone. so i could have

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567
        2395562
        2354892

I would like to pick only one for each type and get the result of

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567

Cheers

greg

 
Old May 16th, 2008, 08:51 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You could use the MAX() or MIN() aggregation function. You'll have to do this for all fields selected that aren't grouped such as:

SELECT name, MAX(day_phone), MAX(Cell_phone), MAX(After_hours)
GROUP BY name

Although I would make the argument that there is no point in storing more than one number for a given type of you are going to simply ignore all but one of them. Database normalization rules would also suggest that you should put all the phone numbers in a single column in a "phone numbers" table and specify the type of each one. This might make the data easier to work with as well.

-Peter
compiledthoughts.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
select statement ??? RinoDM SQL Server 2000 7 June 19th, 2008 08:40 AM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Select Statement jmss66 Oracle 1 May 27th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.