tsql - SQL Server Row_number in odd/even number? -

i have question querying accounting data.

for example, sample data show below

table: table_test

date        amount   2013-01-01  12.00 2013-01-02  13.00 

the output should this:

date        account        debit    credit 2013-01-01  abccompany     12.00 2013-01-01  vendorcompany           12.00 2013-01-02  abccompany     13.00 2013-01-02  vendorcompany           13.00 

initially, think using union statement because may output sequece not important , sample sql show below

select      date 'date',      'abccompany' 'account',      amount 'debit',      '0' credit table_test  union  select     date 'date',      'vendorcompany' 'account',      '0' 'debit',      amount  credit table_test 

output:

date        account        debit    credit 2013-01-01  abccompany     12.00 2013-01-02  abccompany     13.00 2013-01-01  vendorcompany           12.00 2013-01-02  vendorcompany           13.00 

but seem after show output pic, mention wrong sequence quite important them.(used export system)

what came out mind using t-sql manipulate may provide flag isdebit , possible row_number(odd number in first sql, number on second sql union , made logic on it? possible?)

is able provide me idea how deal this?

something should work:

select  date 'date', 'abccompany' 'account', amount 'debit', '0' credit, row_number() on (order date) * 2 rn table_test union date 'date', 'vendorcompany' 'account', '0' 'debit', amount  credit, row_number() on (order date) * 2 + 1 table_test order rn 

although more columns may needed in order by clauses make things unambiguous (it's tad tricky tell limited example you've given)

i've switched using union all since result sets produced distinct.