Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Phone number by type

Author  Topic 

jchoudja
Starting Member

41 Posts

Posted - 2013-02-25 : 09:13:18
Hi, I need help to create an SQL query to list customers phone numbers by type in 3 colums.

customerlist
CustID|Name____|PhoneNumber___|PhoneLocationID
---------------------------------------
1_____|_Jean___|_281 572 2623_|_L1_
1_____|_jean___|_281 342 5242_|_L2_
1_____|_jean___|_281 342 5202_|_L3_
2_____|_Sophia_|_832 340 2222_|_L3_
3_____|_Chad___|_281 557 3566_|_L1_
3_____|_Chad___|_832 778 5222_|_L3_
4_____|_Bill___|_415 725 2544_|_L2_

phoneLocation
PhoneLocationID|LocationNumber|Description
-------------------------------------------
__L1___________|___1__________|Home phone
__L2___________|___2__________|Work phone
__L3___________|___3__________|cell phone

I want to get phone numbers for customer in 3 colums like this.

phoneList
Name____|HomePhone_____|_Workphone____|cellphone_____|
------------------------------------------------------
.Jean...|.281 572 2623.|.281 342 5242.|.281 342 5202.|
.Sophia.|..............|..............|.832 340 2222.|
.Chad...|.281 557 3566.|..............|.832 778 5222.|
.Bill...|..............|.415 725 2544.|..............|

..= empty space

jc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 09:34:36
[code]SELECT
Name,
L1 AS HomePhone,
L2 AS WorkPhone,
L3 AS CellPhone
FROM
Tbl
PIVOT( MAX(PhoneNumber) FOR PhoneLocation IN ([L1],[L2],[L3]))P
[/code]
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-25 : 12:16:41
Thank you . But never used PIVOT before can you please provide some details. What does P stand for?
I already used SELECT CASE to group my phone by location. and here is how I diud it.
1 to 4 here are phone locations where 1 = Home phone1 and 2 Home phone2 etc.

CASE WHEN LocationNumber IN (1, 2) THEN PhonePhoneNumber ELSE '-' END AS Home_phone,
CASE WHEN LocationNumber IN (5, 6) THEN PhonePhoneNumber ELSE '-' END AS Cell_phone,
CASE WHEN LocationNumber IN (3, 4) THEN PhonePhoneNumber ELSE '-' END AS Work_phone,
How can I use the PIVOT from here

Thank you

jc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 12:26:26
PIVOT operator does exactly what the name implies - i.e., it pivots (or transforms a set of rows into columns based on values in the rows). Take a look at this MSDN page - it has documentation and examples - and does a pretty good job of explaining it http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

The P is simply an alias. The full syntax would include the keyword "AS", but I omitted it because it is optional. You can use any valid name for alias.
.....
PIVOT( MAX(PhoneNumber) FOR PhoneLocation IN ([L1],[L2],[L3])) AS P
I didn't follow the query you posted. Can you post the full query?
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-25 : 13:20:41
Here is the actual full query.

Problem with it is that it show only phone numbers in their appropriate columns but in multiple lines. PIVOT sims to be the solution to fix that. But How can I add it in my query below?

SELECT DISTINCT
TOP (100) PERCENT mts.MembershipTypeStatusName, p.PersonFirstName, p.PersonLastName,
CASE WHEN dbo.Attribute.AttributeName = 'POW' THEN 'Yes' ELSE 'No' END AS PowerMember, CASE WHEN PhoneLocationPrecedence IN (1, 2)
THEN PhonePhoneNumber ELSE '-' END AS Home_phone, CASE WHEN PhoneLocationPrecedence IN (5, 6) THEN PhonePhoneNumber ELSE '-' END AS Cell_phone,
CASE WHEN PhoneLocationPrecedence IN (3, 4) THEN PhonePhoneNumber ELSE '-' END AS Work_phone
FROM dbo.PersonPhone INNER JOIN
dbo.Membership AS mm INNER JOIN
dbo.MembershipAgreement AS ma ON mm.MembershipGUID = ma.MembershipGUID INNER JOIN
dbo.Agreement AS a ON ma.AgreementGUID = a.AgreementGUID INNER JOIN
dbo.MembershipTypeStatus AS mts ON mm.MembershipTypeStatusGUID = mts.MembershipTypeStatusGUID INNER JOIN
dbo.Member AS m ON mm.MemberGUID = m.MemberGUID INNER JOIN
dbo.Person AS p ON m.PersonGUID = p.PersonGUID INNER JOIN
dbo.MembershipStatus AS s ON mm.MembershipGUID = s.MembershipGUID AND mts.MembershipTypeStatusGUID = s.MembershipTypeStatusGUID ON
dbo.PersonPhone.PersonGUID = p.PersonGUID INNER JOIN
dbo.Phone ON dbo.PersonPhone.PhoneGUID = dbo.Phone.PhoneGUID INNER JOIN
dbo.PhoneLocation ON dbo.Phone.PhoneLocationGUID = dbo.PhoneLocation.PhoneLocationGUID LEFT OUTER JOIN
dbo.Attribute INNER JOIN
dbo.MemberAttribute ON dbo.Attribute.AttributeGUID = dbo.MemberAttribute.AttributeGUID ON m.MemberGUID = dbo.MemberAttribute.MemberGUID
WHERE (mts.MembershipTypeStatusName = N'Active') AND (ma.MembershipAgreementLastUpdate =
(SELECT MAX(MembershipAgreementLastUpdate) AS Expr1
FROM dbo.MembershipAgreement
WHERE (MembershipGUID = mm.MembershipGUID))) AND (s.MembershipStatusLastUpdate =
(SELECT MAX(MembershipStatusLastUpdate) AS Expr1
FROM dbo.MembershipStatus
WHERE (MembershipGUID = s.MembershipGUID))) OR
(mts.MembershipTypeStatusName = N'Active')
ORDER BY p.PersonFirstName


jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-25 : 13:22:57
Forum doesn't allw me to higlight here is the section where I set pone number?

CASE WHEN PhoneLocationPrecedence IN (1, 2)
THEN PhonePhoneNumber ELSE '-' END AS Home_phone, CASE WHEN PhoneLocationPrecedence IN (5, 6) THEN PhonePhoneNumber ELSE '-' END AS Cell_phone,
CASE WHEN PhoneLocationPrecedence IN (3, 4) THEN PhonePhoneNumber ELSE '-' END AS Work_phone

How can I add pivot here



jc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 14:19:05
See if this will work for you. The changes to your code are in red
SELECT DISTINCT 
TOP(100) PERCENT mts.MembershipTypeStatusName,
p.PersonFirstName,
p.PersonLastName,
CASE
WHEN dbo.Attribute.AttributeName = 'POW' THEN 'Yes'
ELSE 'No'
END AS PowerMember,
MAX(CASE
WHEN PhoneLocationPrecedence IN (1, 2) THEN PhonePhoneNumber
ELSE '-'
END) AS Home_phone,
MAX(CASE
WHEN PhoneLocationPrecedence IN (5, 6) THEN PhonePhoneNumber
ELSE '-'
END) AS Cell_phone,
MAX(CASE
WHEN PhoneLocationPrecedence IN (3, 4) THEN PhonePhoneNumber
ELSE '-'
END) AS Work_phone
FROM dbo.PersonPhone
INNER JOIN dbo.Membership AS mm
INNER JOIN dbo.MembershipAgreement AS ma
ON mm.MembershipGUID = ma.MembershipGUID
INNER JOIN dbo.Agreement AS a
ON ma.AgreementGUID = a.AgreementGUID
INNER JOIN dbo.MembershipTypeStatus AS mts
ON mm.MembershipTypeStatusGUID = mts.MembershipTypeStatusGUID
INNER JOIN dbo.Member AS m
ON mm.MemberGUID = m.MemberGUID
INNER JOIN dbo.Person AS p
ON m.PersonGUID = p.PersonGUID
INNER JOIN dbo.MembershipStatus AS s
ON mm.MembershipGUID = s.MembershipGUID
AND mts.MembershipTypeStatusGUID = s.MembershipTypeStatusGUID
ON dbo.PersonPhone.PersonGUID = p.PersonGUID
INNER JOIN dbo.Phone
ON dbo.PersonPhone.PhoneGUID = dbo.Phone.PhoneGUID
INNER JOIN dbo.PhoneLocation
ON dbo.Phone.PhoneLocationGUID = dbo.PhoneLocation.PhoneLocationGUID
LEFT OUTER JOIN dbo.Attribute
INNER JOIN dbo.MemberAttribute
ON dbo.Attribute.AttributeGUID = dbo.MemberAttribute.AttributeGUID
ON m.MemberGUID = dbo.MemberAttribute.MemberGUID
WHERE (mts.MembershipTypeStatusName = N'Active')
AND (
ma.MembershipAgreementLastUpdate =(
SELECT MAX(MembershipAgreementLastUpdate) AS Expr1
FROM dbo.MembershipAgreement
WHERE (MembershipGUID = mm.MembershipGUID)
)
)
AND (
s.MembershipStatusLastUpdate =(
SELECT MAX(MembershipStatusLastUpdate) AS Expr1
FROM dbo.MembershipStatus
WHERE (MembershipGUID = s.MembershipGUID)
)
)
OR (mts.MembershipTypeStatusName = N'Active')
GROUP BY
mts.MembershipTypeStatusName,
p.PersonFirstName,
p.PersonLastName,
CASE
WHEN dbo.Attribute.AttributeName = 'POW' THEN 'Yes'
ELSE 'No'
END

ORDER BY
p.PersonFirstName
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-25 : 14:54:44
You are a genius. Worked right out of the box. Thank you so mutch.
Pivot wasn't needed.


jc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 17:23:29
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -