sql query result format

Question

Sorry for inconvenience,i thought i am simplifying the question but may be i made it more complex,previously the data was like this,

Table:

BRANCHCD       BAL1    BAL2   ACMCD
SH14           10        -      111
SH14           11        -      112
SH14            -         1     211

in one table had bal1 for acmcd and bal2 for acmcd,onl one will be available at a time so don't worry about that case,so and i need it in following format.

  BRANCHCD       BAL1    ACMCD   bal2  acmcd
    SH14           10      111      1    211
    SH14           11      112

if new rows added in Table are:

BRANCHCD       BAL1    BAL2   ACMCD
SH14                     2      212
SH14                     3      213

then o/p should be

  BRANCHCD       BAL1    ACMCD   bal2  acmcd
    SH14           10      111      1    211
    SH14           11      112      2    212
    SH14                            3    213

thanks for the reply i already tried that and it is not that much useful for my case.. — Vaibhav
– Vaibhav, Commented Aug 10, 2018 at 16:28
i do not understand why bal2 and ACMCD should be null? what is the logic of your join? — hotfix
– hotfix, Commented Aug 10, 2018 at 17:33
when i am having tbl1 join with tbl2 and tbl1 with tbl3 separately then i have 2 rows from 1st join and 1 row from 2nd just need to merge them and get above result. — Vaibhav
– Vaibhav, Commented Aug 10, 2018 at 18:07
@Vaibhav why does the second row do not have data from the third table? why not the second row has values from third table and first don't? how do you know when the row should be joined and when not — hotfix
– hotfix, Commented Aug 10, 2018 at 18:35

Kaushik Nayak · Accepted Answer · 2018-08-12 08:16:06Z

I presume your desired output is to basically compress the new records to show them juxtaposed rather than as new entries below. So, there is no relation between acmcd = 112 and acmcd = 212 other than the fact that they are 2nd available entries under respective "bal"s for a branchcd in the table. It also appears that the column acmcd gets a unique value for each newly added row ( sequentially or not).

If the above statements are true, you could use row_number() to generate ids for each unique acmcds for a given BRANCHCD. Creating bal1 and bal2 records as separate data sets through a cte or sub-query, we could do a FULL OUTER JOIN on row_number.

SQL Fiddle

Query:

WITH a 
     AS (SELECT row_number() 
                  OVER( 
                    partition BY branchcd 
                    ORDER BY acmcd ) AS rn, 
                t.* 
         FROM   t 
         WHERE  bal1 IS NOT NULL), 
     b 
     AS (SELECT row_number() 
                  OVER( 
                    partition BY branchcd 
                    ORDER BY acmcd ) AS rn, 
                t.* 
         FROM   t 
         WHERE  bal2 IS NOT NULL) 
SELECT COALESCE(a.branchcd,b.branchcd) as branchcd,
       a.bal1, 
       a.acmcd, 
       b.bal2, 
       b.acmcd 
FROM   a 
       FULL OUTER JOIN b 
                    ON ( a.branchcd = b.branchcd 
                         AND a.rn = b.rn )

Results:

| BRANCHCD |   BAL1 |  ACMCD | BAL2 | ACMCD |
|----------|--------|--------|------|-------|
|     SH14 |     10 |    111 |    1 |   211 |
|     SH14 |     11 |    112 |    2 |   212 |
|     SH14 | (null) | (null) |    3 |   213 |

Zynon Putney II · Accepted Answer · 2018-08-10 18:56:48Z

0

Unless your tables have more columns that you aren't sharing with us, it seems you need to add to your left join of table C something that compares the last two digits of ACMCD. Otherwise you aren't giving any indication to the query as to how those records are related.

answered Aug 10, 2018 at 18:56

Zynon Putney II

6954 silver badges12 bronze badges

1 Comment

Vaibhav Over a year ago

Sorry for inconvenience please check the question,i edited it

Collectives™ on Stack Overflow

sql query result format

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related