Adding new row from column-CodePudding

My table

emp checking    saving
a   100         500
a   200         1000
c   300         2000
d   300         200
d   100         50

I need to create a second row from saving column so I will need this result

emp    checking
a      100
a      500
a      200
a      1000
c      300
c      2000
d      300
d      200
d      100
d      50

I tried this query

select t.emp, v.checking, v.saving
from Table1 t
cross apply (values
                 (1, checking, null),
                 (2, null, saving)
            ) v (n, checking, saving)
order by T.emp, v.n;

and I get this

emp checking    saving
a   200 NULL
a   100 NULL
a   NULL    500
a   NULL    1000
c   300 NULL
c   NULL    2000
d   300 NULL
d   100 NULL
d   NULL    50
d   NULL    200

Can someone see what went wrong and correct it. Thanks

I need to bring this back cause if I use this query

select emp, checking
from  table1
union all
select emp, saving
from  table1
order by emp

then I get this

emp checking
a   100
a   200
a   500
a   1000
c   2000
c   300
d   300
d   100
d   200
d   50

and still not correct. Please if you have better idea. Thank you

CodePudding user response：

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (emp CHAR(1), checking INT, saving INT);
INSERT @tbl (emp, checking, saving) VALUES
('a', 100, 500 ),
('a', 200, 1000),
('c', 300, 2000),
('d', 300, 200 ),
('d', 100, 50  );
-- DDL and sample data population, start

SELECT t.emp, v.checking
FROM @tbl AS t
CROSS APPLY (VALUES
                (checking),
                (saving)
            ) AS v (checking)
ORDER BY t.emp;

Output

emp	checking
a	100
a	500
a	200
a	1000
c	300
c	2000
d	300
d	200
d	100
d	50

CodePudding user response：

Building on @Yitzhak's excellent answer, and assuming you have some form of id per row (because otherwise you cannot ensure the order you desire), I would do the following to ensure you get the exact order you want. While its possible CROSS APPLY will always be ordered correctly, in SQL its usually best to explicitly order a dataset if the order is important.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id int, emp char(1), checking int, saving int);

INSERT @tbl (id, emp, checking, saving)
VALUES
(1, 'a', 100, 500),
(2, 'a', 200, 1000),
(3, 'c', 300, 2000),
(4, 'd', 300, 200),
(5, 'd', 100, 50);

SELECT t.emp, v.checking
FROM @tbl AS t
CROSS APPLY (
    VALUES
    (checking, 0),
    (saving, 1)
) AS v (checking, orderby)
ORDER BY t.emp, t.id, v.orderBy;