Oracle如何从table_1表中查询值为10的记录并逐行返回指定格式结果

要实现该查询需求，可使用Oracle的UNPIVOT行转列语法完成，SQL语句如下：

SELECT 
  id,
  REGEXP_REPLACE(day_col, 'day_', '') AS DAY,
  val AS ID2
FROM table_1
UNPIVOT (
  val FOR day_col IN (day_1, day_2, day_3, day_4, day_5)
)
WHERE val = 10
ORDER BY id;

如果你使用的Oracle版本不支持UNPIVOT语法，也可以用UNION ALL手动拼接各天的查询结果：

SELECT id, '1' AS DAY, day_1 AS ID2 FROM table_1 WHERE day_1 = 10
UNION ALL
SELECT id, '2' AS DAY, day_2 AS ID2 FROM table_1 WHERE day_2 = 10
UNION ALL
SELECT id, '3' AS DAY, day_3 AS ID2 FROM table_1 WHERE day_3 = 10
UNION ALL
SELECT id, '4' AS DAY, day_4 AS ID2 FROM table_1 WHERE day_4 = 10
UNION ALL
SELECT id, '5' AS DAY, day_5 AS ID2 FROM table_1 WHERE day_5 = 10
ORDER BY id;

以上两种写法执行后都能得到你要求的查询结果。

内容的提问来源于stack exchange，提问作者babayaro