October 6, 2010 at 3:57 pm
In my database I have a contact_phone table. The type of phone it is, is indicated in the contact_phone_type_KEY.
1 = Business
2 = Fax
4 = Home
5 = Cell
This table is linked to the contact table by the contact_KEY field. I need all the phone numbers to be on the same row in a result set when I join the tables.
Example:
name, contact_KEY, salutation..., business_phone, fax_phone, home_phone, cell_phone.
How can I achieve this? My starting query is below:
SELECT Contact.name, Contact.contact_KEY, Contact.salutation, Contact.mailing__contact_address_type_KEY, Contact_Address.address_1, Contact_Address.city,
Contact_Address.address_2, Contact_Address.state_abbreviation, Contact_Address.postal_code, Contact_Address.contact_address_type_KEY, Client.client_id,
Contact_Phone.phone
FROM Contact INNER JOIN
Contact_Address ON Contact.contact_KEY = Contact_Address.contact_KEY AND
Contact.mailing__contact_address_type_KEY = Contact_Address.contact_address_type_KEY INNER JOIN
Client ON Contact.contact_KEY = Client.contact_KEY INNER JOIN
Contact_Phone ON Contact.contact_KEY = Contact_Phone.contact_KEY
Thank you!
October 6, 2010 at 4:27 pm
norbertackerman
You will be likely to get a tested answer if you post the table definitions and some sample data along with expected results following the method given in the first link in my signature block.
The sample data should be similiar to your real data, but NOT data that would reveal confidental information, i.e., use dummy names, phone numbers etc.
October 11, 2010 at 4:27 pm
Hope this helps in explaining what I need:
Ideal Result
client_id namecontact_KEYsalutationmailing__contact_address_type_KEYaddress_1cityaddress_2state_abbreviationpostal_codecontact_address_type_KEY Business Fax Home Mobile
CREATE TABLE [dbo].[Contact_Phone](
[contact_phone_KEY] [int] IDENTITY(1,1) NOT NULL,
[update__staff_KEY] [int] NOT NULL,
[update_date] [datetime] NOT NULL,
[contact_KEY] [int] NOT NULL,
[contact_phone_type_KEY] [int] NOT NULL,
[phone] [nvarchar](25) NOT NULL,
[extension] [nvarchar](5) NOT NULL,
[create_date] [datetime] NOT NULL,
CONSTRAINT [PK_Contact_Phone] PRIMARY KEY CLUSTERED
(
[contact_phone_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [UK_Contact_Phone__contact_KEY__contact_phone_type_KEY] UNIQUE NONCLUSTERED
(
[contact_KEY] ASC,
[contact_phone_type_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Sample Data:
215522008-08-04 12:00:50.10739012(626) 796-12342008-08-04 12:00:50.107
315522008-08-04 12:00:50.67039021(626) 796-12342022008-08-04 12:00:50.670
415522008-08-04 12:00:50.67039022(626) 796-12342008-08-04 12:00:50.670
CREATE TABLE [dbo].[Contact_Phone_Type](
[contact_phone_type_KEY] [int] IDENTITY(1,1) NOT NULL,
[update__staff_KEY] [int] NOT NULL,
[update_date] [datetime] NOT NULL,
[description] [nvarchar](30) NOT NULL,
CONSTRAINT [PK_Contact_Phone_Type] PRIMARY KEY CLUSTERED
(
[contact_phone_type_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample data:
102004-10-02 16:48:42.320Business
202004-04-06 19:41:35.000Fax
302004-04-06 19:41:50.000Car
402004-04-06 19:41:54.000Home
502004-04-06 19:41:57.000Mobile
CREATE TABLE [dbo].[Contact](
[contact_KEY] [int] NOT NULL,
[update__staff_KEY] [int] NOT NULL,
[update_date] [datetime] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[company] [nvarchar](50) NOT NULL,
[title] [nvarchar](20) NOT NULL,
[salutation] [nvarchar](40) NOT NULL,
[phone_position] [nvarchar](14) NOT NULL,
[address_position] [nvarchar](4) NOT NULL,
[email_position] [nvarchar](6) NOT NULL,
[primary__contact_phone_type_KEY] [int] NOT NULL,
[primary__contact_address_type_KEY] [int] NOT NULL,
[mailing__contact_address_type_KEY] [int] NOT NULL,
[contact_type_KEY] [int] NOT NULL,
[file_as] [nvarchar](50) NOT NULL,
[create_date] [datetime] NOT NULL,
[contact_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[primary__contact_email_type_KEY] [int] NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
(
[contact_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Contact__contact_guid] UNIQUE NONCLUSTERED
(
[contact_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample Data:
3901155319:05.0John Doe1CompanyPresidentJohn1;2;3;4;5;6;7;1114Doe, John41:37.2264B8C87-9BAD-4A01-B0C0-8DBA66D5A41F1
3902155341:38.6John Doe2CompanyPartnerJohn4;1;2;5;3;6;7;1;2;4112Doe, John00:50.7D7FF485E-5FCC-4029-8DF3-3F9C51127ABD1
3903155346:06.7John Doe3CompanyJohn4;1;5;2;3;6;7;2;4222Doe, John00:51.2D575DF60-D46D-4B0D-AA54-52A47C2634DC1
October 11, 2010 at 5:29 pm
norbertackerman (10/6/2010)
In my database I have a contact_phone table. The type of phone it is, is indicated in the contact_phone_type_KEY.1 = Business
2 = Fax
4 = Home
5 = Cell
This table is linked to the contact table by the contact_KEY field. I need all the phone numbers to be on the same row in a result set when I join the tables.
Sounds like you need to PIVOT the data. Please check out the two links in my signature for Cross-Tab and Pivot tables, Part 1 and Part 2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 1:03 pm
Wayne,
It looks like this is what I need. However, I keep getting an error because the the phone field is not int.
SELECT contact_KEY,
SUM(CASE WHEN contact_phone_type_KEY = 1 THEN phone ELSE 0 END) AS [Business],
SUM(CASE WHEN contact_phone_type_KEY = 2 THEN phone ELSE 0 END) AS [Fax],
SUM(CASE WHEN contact_phone_type_KEY = 4 THEN phone ELSE 0 END) AS [Home],
SUM(CASE WHEN contact_phone_type_KEY = 5 THEN phone ELSE 0 END) AS [Cell]
FROM dbo.Contact_Phone
GROUP BY contact_KEY
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '(626) 123-4567' to data type int.
October 20, 2010 at 8:24 pm
norbertackerman (10/12/2010)
Wayne,It looks like this is what I need. However, I keep getting an error because the the phone field is not int.
SELECT contact_KEY,
SUM(CASE WHEN contact_phone_type_KEY = 1 THEN phone ELSE 0 END) AS [Business],
SUM(CASE WHEN contact_phone_type_KEY = 2 THEN phone ELSE 0 END) AS [Fax],
SUM(CASE WHEN contact_phone_type_KEY = 4 THEN phone ELSE 0 END) AS [Home],
SUM(CASE WHEN contact_phone_type_KEY = 5 THEN phone ELSE 0 END) AS [Cell]
FROM dbo.Contact_Phone
GROUP BY contact_KEY
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '(626) 123-4567' to data type int.
Just for grins, try changing it from SUM(CASE... to MAX(CASE... for each line.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply