Duplicating SQL rows in SQL Server

Question

I have table which has columns for credit and debit account as rows of periods '1-12' and also a opening balance which should carry into each period.

Table

Account  |Period   |Credit |Debits    |Opening Balance
1000       1          100     0           50          
1000       2           0      100         50          
.          .           .      .            .          
.          .           .      .            .          
1001       1          50      0           100         
1002       1          40      0           100

But my problem here is can I duplicate or create 12 row with opening balance in each row for account 1001 and 1002??

you want to insert a blank row? I'm not sure I understand the question — Matt Busche
– Matt Busche, Commented Feb 26, 2013 at 3:21
In the above table i want to dynamically create a row say for Account ID 1001 (1001 2 0 0 100 and 1001 3 0 0 100 and so on)..thanks — Dash600028
– Dash600028, Commented Feb 26, 2013 at 3:27

Gordon Linoff · Accepted Answer · 2013-02-26 03:27:21Z

1

Here is one method. It uses a join to a list of numbers to get all the combinations of periods and accounts. It then uses a correlated subquery to choose the most recent value for the balance.

This version uses MySQL syntax. The correlated subquery might use select top 1 or where rownum = 1 or something else depending on the database:

select t.account, n.n, coalesce(t.credit, 0) as credit,
       coalesce(t.debit, 0) as debit,
       (select balance
        from t t2
        where t2.account = t.account and
              t2.period <= t.period
        order by period desc
        limit 1
       ) as balance
from (select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12
     ) n left outer join
     t
     on t.period = n.n

answered Feb 26, 2013 at 3:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dash600028 Over a year ago

Still I can get only one row even though left Outer join should return all the 12 rows?? Am I doing anything wrong??

Dash600028 Over a year ago

Think the problem here is it creates 12 rows but assigns 'NULL' in account column,but I want the account also to be duplicated..regards

Collectives™ on Stack Overflow

Duplicating SQL rows in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related