Adding new row from column

Question

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

I suggest to UNION of two simple SELECT statements with ORDER BY at the end (if required). — PM 77-1
– PM 77-1, Commented Aug 31, 2022 at 21:26
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Aug 31, 2022 at 21:27
@YitzhakKhabinsky - OP already provided enough. Your requirements are super overkill. — PM 77-1
– PM 77-1, Commented Aug 31, 2022 at 21:29

Yitzhak Khabinsky · Accepted Answer · 2022-08-31 22:26:57Z

3

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

answered Aug 31, 2022 at 22:26

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

3 Comments

Dale K Over a year ago

Just curious, is a CROSS APPLY guaranteed to always be ordered in the order calculated?

Yitzhak Khabinsky Over a year ago

Just testing with a bigger data set will reveal it

Yitzhak Khabinsky Over a year ago

Any table suppose to have a primary key. This exactly what is missing here

Dale K · Accepted Answer · 2022-08-31 23:01:53Z

1

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;

answered Aug 31, 2022 at 23:01

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

1 Comment

Tsang Over a year ago

Cool, another great point to validate data.

Collectives™ on Stack Overflow

Adding new row from column

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related