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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help- display mulitple row results into columns

Author  Topic 

billm
Starting Member

2 Posts

Posted - 2014-10-17 : 08:58:35
The following query provides multiple phone results for the same acct_id

I am pulling data from 3 separate tables

I want to have one row per acct_id and add phone numbers if there is a result



SELECT RTRIM(Accounts.ACCT_ID) AS AcctID, RTRIM(Accounts.ACCT_SSNUM) AS SSN, RTRIM(Accounts.ACCT_FIRST_NAME) AS FirstName,
RTRIM(Accounts.ACCT_LAST_NAME) AS LastName, RTRIM(Accounts.ACCT_AD1) AS DeliveryAddress, RTRIM(Accounts.ACCT_AD2) AS SecondaryAddress,
RTRIM(Accounts.ACCT_CITY) AS City, RTRIM(Accounts.ACCT_ST) AS State, RTRIM(Accounts.ACCT_ZIP) AS Zip, RTRIM(PHNMBR_PHONE_NUMERIC) AS HomePhone
, RTRIM(Accounts.ACCT_DOB)
AS dob
FROM Accounts INNER JOIN
CA1B ON Accounts.ACCT_ID = CA1B.CLNT_ACCT_REF INNER JOIN
Phone_Numbers ON Accounts.ACCT_ID = Phone_Numbers.PHNMBR_ACCT_ID
WHERE
(Accounts.ACCT_CLIENT1 = 'joe')
AND (Accounts.ACCT_DATE_ADDED = '20130122')
AND (CA1B.DateTime = '1/23/2013 9:57:47 AM' OR
CA1B.DateTime = '1/24/2013 9:12:19 AM')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 09:46:05
Something like this (Couldn't test it since I don't have your data):


SELECT RTRIM(Accounts.ACCT_ID) AS AcctID
,RTRIM(Accounts.ACCT_SSNUM) AS SSN
,RTRIM(Accounts.ACCT_FIRST_NAME) AS FirstName
,RTRIM(Accounts.ACCT_LAST_NAME) AS LastName
,RTRIM(Accounts.ACCT_AD1) AS DeliveryAddress
,RTRIM(Accounts.ACCT_AD2) AS SecondaryAddress
,RTRIM(Accounts.ACCT_CITY) AS City
,RTRIM(Accounts.ACCT_ST) AS STATE
,RTRIM(Accounts.ACCT_ZIP) AS Zip
,phone.numbers
,RTRIM(Accounts.ACCT_DOB) AS dob
FROM Accounts
INNER JOIN CA1B ON Accounts.ACCT_ID = CA1B.CLNT_ACCT_REF
CROSS APPLY (
SELECT RTRIM(PHNMBR_PHONE_NUMERIC) + ' '
FROM Phone_Numbers
WHERE Accounts.ACCT_ID = Phone_Numbers.PHNMBR_ACCT_ID
FOR XML path('')
) phone(numbers)
WHERE (Accounts.ACCT_CLIENT1 = 'joe')
AND (Accounts.ACCT_DATE_ADDED = '20130122')
AND (
CA1B.DATETIME = '1/23/2013 9:57:47 AM'
OR CA1B.DATETIME = '1/24/2013 9:12:19 AM'
)
Go to Top of Page

billm
Starting Member

2 Posts

Posted - 2014-10-17 : 10:14:43
that is a fantastic start!

the phone results were displayed in one column instead of multiple columns(one column per phone number)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 11:17:36
Yes, you didn't ask for multiple columns. To get multiple columns is a PIVOT problem and a dynamic SQL one at that.
Go to Top of Page
   

- Advertisement -