Transposing Column Data into Row Data SQL

I have some data that looks like this in an SQL table.

[ID],[SettleDate],[Curr1],[Curr2][Quantity1],[Quantity2],[CashAmount1],[CashAmount2]

The issue i have, i need to create 2 records from this data (all information from 1 and all information of 2). Example below.

[ID],[SettleDate],[Curr1],[Quantity1],[CashAmount1]
[ID],[SettleDate],[Curr2],[Quantity2],[CashAmount2]

Does anyone have an ideas how to do so?

Thanks

Answer

A standard (ie cross-RDBMS) solution for this is to use union:

select ID, SettleDate, Curr1, Quantity1, CashAmount1 from mytable
union all select ID, SettleDate, Curr2, Quantity2, CashAmount2 from mytable

Depending on your RBDMS, neater solutions might be available.