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 name contact_KEY salutation mailing__contact_address_type_KEY address_1 city address_2 state_abbreviation postal_code contact_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:
2 1552 2008-08-04 12:00:50.107 3901 2 (626) 796-1234 2008-08-04 12:00:50.107
3 1552 2008-08-04 12:00:50.670 3902 1 (626) 796-1234 202 2008-08-04 12:00:50.670
4 1552 2008-08-04 12:00:50.670 3902 2 (626) 796-1234 2008-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:
1 0 2004-10-02 16:48:42.320 Business
2 0 2004-04-06 19:41:35.000 Fax
3 0 2004-04-06 19:41:50.000 Car
4 0 2004-04-06 19:41:54.000 Home
5 0 2004-04-06 19:41:57.000 Mobile
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:
3901 1553 19:05.0 John Doe1 Company President John 1;2;3;4;5;6;7; 1 1 1 4 Doe, John 41:37.2 264B8C87-9BAD-4A01-B0C0-8DBA66D5A41F 1
3902 1553 41:38.6 John Doe2 Company Partner John 4;1;2;5;3;6;7; 1;2; 4 1 1 2 Doe, John 00:50.7 D7FF485E-5FCC-4029-8DF3-3F9C51127ABD 1
3903 1553 46:06.7 John Doe3 Company John 4;1;5;2;3;6;7; 2; 4 2 2 2 Doe, John 00:51.2 D575DF60-D46D-4B0D-AA54-52A47C2634DC 1
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