SQL Server Performance Forum – Threads Archive

Query Returns 2 Rows For same Subject

I am building a query so I can more easily export accounts. The accounts sometimes contain info on 2 persons in the same account and I want to display both persons info in one row instead of 2 rows. Here is part of the query. SELECT
(select mytable.number where mytable.seq = ‘0’) AS ‘NUMBER’,
(select master.account where mytable.seq = ‘0’) AS ‘CUST_ACCT#’,
(select master.status where mytable.seq = ‘0’) AS ‘STATUS’,
(select master.id1 where mytable.seq = ‘0’) AS ‘CLIENT_ID’,
(select master.originaldate where mytable.seq = ‘0’) AS ‘ORIG_DATE’,
(select mytable.lastname where mytable.seq = ‘0’) AS ‘PERSON1_LNAME’,
(select mytable.firstname where mytable.seq = ‘0’) AS ‘PERSON1_FNAME’,
(select mytable.middlename where mytable.seq = ‘0’) AS ‘PERSON1_MI’,
(select mytable.street1 where mytable.seq = ‘0’) AS ‘PERSON1_ADDR1’,
(select mytable.street2 where mytable.seq = ‘0’) AS ‘PERSON1_ADDR2’,
(select mytable.city where mytable.seq = ‘0’) AS ‘PERSON1_CITY’,
(select mytable.state where mytable.seq = ‘0’) AS ‘PERSON1_STATE’,
(select mytable.zipcode where mytable.seq = ‘0’) AS ‘PERSON1_ZIP’,
(select mytable.ssn where mytable.seq = ‘0’) AS ‘PERSON1_SSN’,
(select mytable.dob where mytable.seq = ‘0’) AS ‘PERSON1_DOB’,
(select mytable.lastname where mytable.seq = ‘1’) AS ‘PERSON2_LNAME’,
(select mytable.firstname where mytable.seq = ‘1’) AS ‘PERSON2_FNAME’,
(select mytable.middlename where mytable.seq = ‘1’) AS ‘PERSON2_MI’,
(select debtors.street1 where mytable.seq = ‘1’) AS ‘PERSON2_ADDR1’,
(select mytable.street2 where mytable.seq = ‘1’) AS ‘PERSON2_ADDR2’,
(select mytable.city where mytable.seq = ‘1’) AS ‘PERSON2_CITY’,
(select mytable.state where mytable.seq = ‘1’) AS ‘PERSON2_STATE’,
(select mytable.zipcode where mytable.seq = ‘1’) AS ‘PERSON2_ZIP’,
(select mytable.ssn where mytable.seq = ‘1’) AS ‘PERSON2_SSN’,
(select mytable.dob where mytable.seq = ‘1’) AS ‘PERSON2_DOB’,
(select master.original1 where mytable.seq = ‘0’) AS ‘AMT_PLACED’,
(select master.Accrued2 where mytable.seq = ‘0’) AS ‘TOTAL_INTREST’,
(select master.current0 where mytable.seq = ‘0’) AS ‘CUR_TOTAL_DUE’,
(select master.current1 where mytable.seq = ‘0’) AS ‘CUR_PRINC_DUE’,
(select master.current2 where mytable.seq = ‘0’) AS ‘CUR_INTR_DUE’,
(select master.paid] where mytable.seq = ‘0’) AS ‘TOTAL_PAID’,
(select master.paid1 where mytable.seq = ‘0’) AS ‘PAID_PRINCIPLE’,
(select master.paid4 where mytable.seq = ‘0’) AS ‘PAID_NSF_CHARGES’,
(select master.paid10 where mytable.seq = ‘0’) AS ‘PAID_TRANS_SURCHARGES’
FROM master
INNER JOIN mytable ON mytable.number=master.number
WHERE (master.customer = ‘0004192’) When an account contains 2 persons, the return hase one row that contains all info except (PERSON2). Below that will be another row that contains only (PERSON2) in the correct columns. I wish to have all account info in one column. Thanks
Mark Stopkey

---

Hi,
Firstly You need to re-write your query.
Consider this example:
select mytable.columns,master.columns from master join mytable on condition
WHERE mytable.seq IN(0,1) and master.customer=’0004192′
This will return you two rows and then you merge this at front end as per your reporting structure

---

]]>