SQL Server Performance

Query Returns 2 Rows For same Subject

Discussion in 'SQL Server Reporting Services' started by mstopkey, Jul 13, 2007.

  1. mstopkey New Member

    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
  2. ranjitjain New Member

    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

Share This Page