January 6, 2012 at 9:47 am
I am creating a view to display data from several tables. Need some assistance with figuring out how to do the following:
Table 'Client'
Fields:ClientID, Firstname, Lastname
Table 'Telephone'
Fields:TelephoneID, ClientID, Number, Type (where Type is 'Home', 'Office', 'Fax', 'Cell')
The view needs to display FirstName, LastName, OfficePhone, HomePhone, CellNumber, FaxNumber
I'm not sure how to write the query to filter by Type and get the correct phone numbers in the view.
Thanks
January 6, 2012 at 9:51 am
WHERE Type IN ( 'Home', 'Office', 'Fax', 'Cell')
January 6, 2012 at 11:18 am
I can use the Where IN clause to filter results for just Home, Office, Fax, and Cell. This will still return a row for each record in the Telephone table.
What I'd really like to do though is something like this:
Table Client
ClientID FirstName LastName
1 Jeff Jones
Table Telephone
TelephoneID ClientID Number Type
1 1 1234567890 Home
2 1 1234567891 Office
3 1 1234567892 Cell
4 1 1234567893 Fax
View results:
FirstName LastName HomePhone OfficePhone CellNumber FaxNumber
Jeff Jones 1234567890 1234567891 1234567892 1234567893
Thanks
January 6, 2012 at 11:19 am
Look up the section on PIVOT in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2012 at 9:34 am
TK-484813 (1/6/2012)
I can use the Where IN clause to filter results for just Home, Office, Fax, and Cell. This will still return a row for each record in the Telephone table.What I'd really like to do though is something like this:
Table Client
ClientID FirstName LastName
1 Jeff Jones
Table Telephone
TelephoneID ClientID Number Type
1 1 1234567890 Home
2 1 1234567891 Office
3 1 1234567892 Cell
4 1 1234567893 Fax
View results:
FirstName LastName HomePhone OfficePhone CellNumber FaxNumber
Jeff Jones 1234567890 1234567891 1234567892 1234567893
Thanks
Since you're new to forums, please read the following article for how to get better answers quicker the next time you post...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
The following code builds some test data for your problem using the methods in the article above so you have another example and so we can demonstrate one solution to this problem...
--===== Conditionally drop the test tables to make reruns in SSMS easier.
-- Note that this is not a part of the solution.
IF OBJECT_ID('tempdb..#Client' ,'U') IS NOT NULL DROP TABLE #Client;
IF OBJECT_ID('tempdb..#Telephone','U') IS NOT NULL DROP TABLE #Telephone;
--===== Create and populate the test table for the client data.
-- Note that this is not a part of the solution.
CREATE TABLE #Client
(
ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL
)
;
INSERT INTO #Client
(FirstName, LastName)
SELECT 'Jeff' , 'Jones' UNION ALL
SELECT 'John' , 'Smith' UNION ALL
SELECT 'Sally', 'Smith'
;
--===== Create and populate the test table for the telephone data.
-- Note that this is not a part of the solution.
CREATE TABLE #Telephone
(
TelephoneID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ClientID INT NOT NULL,
Number CHAR(10) NOT NULL CHECK (Number NOT LIKE '%[^0-9]%'),
Type CHAR(6) NOT NULL
)
;
INSERT INTO #Telephone
(ClientID,Number,Type)
SELECT 1,'1234567890','Home' UNION ALL
SELECT 1,'1234567891','Office' UNION ALL
SELECT 1,'1234567892','Cell' UNION ALL
SELECT 1,'1234567893','Fax' UNION ALL
SELECT 2,'2234567890','Home' UNION ALL
SELECT 2,'2234567891','Office' UNION ALL
SELECT 2,'2234567892','Cell' UNION ALL
SELECT 3,'1234567891','Office' UNION ALL
SELECT 3,'1234567892','Cell' UNION ALL
SELECT 3,'1234567893','Fax'
;
If you take the time to post your problem like that (has readily consumable test data), folks will climb over each other trying to help you instead of just giving you suggestions.
Here's one solution that uses the test data above. You'll need to change the table names to match your own...
--===== Solve the problem using a CROSS TAB
SELECT FirstName ,
LastName,
HomePhone = MAX(CASE WHEN t.Type = 'Home' THEN Number ELSE '' END),
OfficePhone = MAX(CASE WHEN t.Type = 'Office' THEN Number ELSE '' END),
CellNumber = MAX(CASE WHEN t.Type = 'Cell' THEN Number ELSE '' END),
FaxNumber = MAX(CASE WHEN t.Type = 'Fax' THEN Number ELSE '' END)
FROM #Client c
LEFT OUTER JOIN #Telephone t
ON c.ClientID = t.ClientID
WHERE t.Type IN ('Home','Office','Cell','Fax')
GROUP BY c.ClientID, FirstName, LastName
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply