SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Phone number by type
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jchoudja
Starting Member

USA
41 Posts

Posted - 02/25/2013 :  09:13:18  Show Profile  Reply with Quote
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

Edited by - jchoudja on 02/25/2013 09:25:44

James K
Flowing Fount of Yak Knowledge

3744 Posts

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

jchoudja
Starting Member

USA
41 Posts

Posted - 02/25/2013 :  12:16:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/25/2013 :  12:26:26  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/25/2013 :  13:20:41  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/25/2013 :  13:22:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/25/2013 :  14:19:05  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/25/2013 :  14:54:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/25/2013 :  17:23:29  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000