How to use data from multiple similar columns as rows in SQL

Question

I have a table as below:

ItemName	1mth_presale_cost	2mth_presale_cost	1mth_postsale_cost	2mth_postsale_cost
1000	10.1	12.1	12.5	15.1
1001	20.2	15.2	25.2	17.3

I want the result to be like below table:

ItemName	1mth_cost	2mth_cost
1000	10.1	12.1
1000	12.5	15.1
1001	20.2	15.2
1001	25.2	17.3

I don't want to use UNION for this.

And why you don't want to use UNION!?

user8103263
– user8103263

2021-09-25 17:56:10 +00:00
Commented Sep 25, 2021 at 17:56 — user8103263
– user8103263, Commented Sep 25, 2021 at 17:56
Just figuring out if there is any other way to do it.

Justalearner
– Justalearner

2021-09-25 18:00:16 +00:00
Commented Sep 25, 2021 at 18:00 — Justalearner
– Justalearner, Commented Sep 25, 2021 at 18:00

Rahul Biswas · Accepted Answer · 2021-09-25 18:16:40Z

2

First each row converted into two sets then apply conditional CASE statement for getting desired result.

-- SQL Server
SELECT t.ItemName
     , CASE WHEN p.id = 1 THEN [1mth_presale_cost] ELSE [1mth_postsale_cost] END  "1mth_cost"
     , CASE WHEN p.id = 1 THEN [2mth_presale_cost] ELSE [2mth_postsale_cost] END  "2mth_cost"
FROM test t
CROSS JOIN (SELECT 1 id UNION SELECT 2 id) p
ORDER BY t.ItemName, p.id

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3e1fdaf829c39554941c068b0c8a3e04

edited Sep 25, 2021 at 18:16

answered Sep 25, 2021 at 18:10

Rahul Biswas

3,5072 gold badges13 silver badges20 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-09-25 18:40:32Z

0

You want to unpivot the data. In SQL Server, you can use cross apply:

select t.ItemName, v.1mth_cost, v.2mth_cost
from t cross apply
     (values (t.1mth_presale_cost, t.2mth_presale_cost, 'presale'),
             (t.1mth_postsale_cost, t.2mth_postsale_cost, 'postsale')
     ) v(1mth_cost, 2mth_cost, which);

Note that this you can also include which to see which of the columns is being used for each row.

answered Sep 25, 2021 at 18:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to use data from multiple similar columns as rows in SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related