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.customerlistCustID|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_phoneLocationPhoneLocationID|LocationNumber|Description-------------------------------------------__L1___________|___1__________|Home phone__L2___________|___2__________|Work phone__L3___________|___3__________|cell phoneI want to get phone numbers for customer in 3 colums like this. phoneListName____|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 spacejc |
|
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 CellPhoneFROM TblPIVOT( MAX(PhoneNumber) FOR PhoneLocation IN ([L1],[L2],[L3]))P[/code] |
|
|
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 hereThank youjc |
|
|
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).aspxThe 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? |
|
|
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_phoneFROM 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.MemberGUIDWHERE (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.PersonFirstNamejc |
|
|
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_phoneHow can I add pivot herejc |
|
|
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 redSELECT 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_phoneFROM 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.MemberGUIDWHERE (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 |
|
|
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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-25 : 17:23:29
|
You are very welcome - glad to help. |
|
|
|