Get data as row per row-CodePudding

How get result as:

Get all from table_1 where ORACLE (ID = 10)

ID   DAY  ID2
---------------
1    1    10
2    2    10
3    3    10
4    4    10

Structure:

Create table table_1 (
id number primary key,
day_1 number,
day_2 number,
day_3 number,
day_4 number,
day_5 number
)


Insert into table_1 (id,day_1,day_2,day_3,day_4,day_5) values (1,10,null,null,null,null);
Insert into table_1 (id,day_1,day_2,day_3,day_4,day_5) values (2,20,10,20,null,null);
Insert into table_1 (id,day_1,day_2,day_3,day_4,day_5) values (3,null,null,10,null,null);
Insert into table_1 (id,day_1,day_2,day_3,day_4,day_5) values (4,null,null,null,10,null);
Insert into table_1 (id,day_1,day_2,day_3,day_4,day_5) values (5,30,null,null,null,null);

--Note

10 - ORACLE

20 - MSSQL

30 - MYSQL

CodePudding user response：

Use UNPIVOT:

SELECT *
FROM   table_1
UNPIVOT (
  id2 FOR day IN (
    day_1 AS 1,
    day_2 AS 2,
    day_3 AS 3,
    day_4 AS 4,
    day_5 AS 5
  )
)
WHERE id2 = 10;

Which, for your sample data, outputs:

ID DAY ID2

1 1 10

2 2 10

3 3 10

4 4 10

ID	DAY	ID2
1	1	10
2	2	10
3	3	10
4	4	10

db<>fiddle here