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

Answer 1

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 |