Split row into multiple rows

Question

I have this table:

+---------+------+---------+
|  Col1   | Col2 |  Col3   |
+---------+------+---------+
| PersonA | $10  | PersonB |
| PersonC | $20  | PersonD |
+---------+------+---------+

I want result like this:

+---------+-----+-----+
|  Col1   | Db  | Cr  |
+---------+-----+-----+
| PersonA | 0   | $10 |
| PersonB | $10 | 0   |
| PersonC | 0   | $20 |
| PersonD | $20 | 0   |
+---------+-----+-----+

Is there any way without using Union all?

Answer 1

If col1 stands for CR and col3 for DB, you can use UNION ALL as below to get your desired output-

SELECT col1 PersonName, 
'$0' DB,
col2 CR    
FROM your_table

UNION ALL

SELECT col3 PersonName,
col2 DB, 
'$0' CR    
FROM your_table

To keep the transaction order as original, you need to create ROW_NUMBER first and then order by RN. ROW_NUMBER creation is different in different database. As you did not mention the database name, I can not add that logic here.

As you are looking for avoiding UNION ALL, you can think about UNPIVOT as below if you are using MSSQL-

WITH CTE AS
(
    SELECT U.PersonName,
    CASE WHEN U.name = 'Col3' THEN U.Col2 ELSE '$0' END AS Db,
    CASE WHEN U.name = 'Col1' THEN U.Col2 ELSE '$0' END AS Cr,
    U.name ValuesFromColumn -- Just print this for better understanding
    FROM your_table S
    UNPIVOT
    (
        PersonName
        FOR NAME IN (col1,col3)
    ) U
)

-- Now select from CTE and Join other table as

SELECT * 
FROM CTE A
LEFT JOIN Other_Table B ON....

-- OR Like

SELECT * 
FROM Other_Table A
LEFT JOIN CTE B ON....

UNPIVOT is also available in other databases. You can adjust Syntax accordingly if you are using other database.