sql - Oracle将表从行转换为列

我有一个查询:

select vrec, valnum, valte from val_tb where
recd in (select recd from rectb where setd = 17)
AND (vid = 3 OR vid = 26 OR vid = 28);

对于上面的结果，我得到:

vrec      valnum       valte
98945823  NULL         Total
98945823  NULL         06001
98945823  16.57        NULL
98945824  NULL         Total
98945824  NULL         06005
98945824  0.36         NULL

我想将其转换为:

98945823    06001   Total   16.57
98945824    06005   Total   0.36

即按vrec合并结果。

是否可以使用Oracle SQL做到这一点？

最佳答案

区分valte值的一种方法是检查字符串是否仅包含数字(较差的解决方案，但应该可以使用):

WITH cte( vrec,valnum, valte) AS
(
  SELECT 98945823 AS vrec,   NULL AS valnum,'Total' AS valte FROM dual
  UNION ALL SELECT 98945823, NULL,  '06001'      FROM dual
  UNION ALL SELECT 98945823, 16.57,  NULL        FROM dual
  UNION ALL SELECT 98945824, NULL,  'Total'      FROM dual
  UNION ALL SELECT 98945824, NULL,  '06005'      FROM dual
  UNION ALL SELECT 98945824, 0.36,  NULL         FROM dual
)
SELECT
    vrec
   ,MAX(CASE WHEN REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END)
   ,MAX(CASE WHEN NOT REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END)
   ,MAX(valnum)
FROM cte
GROUP BY vrec;

SqlFiddleDemo

输出:

╔═══════════╦═══════════════╦═══════════════╦═════════════╗
║   VREC    ║ MAX(CASE...)  ║ MAX(CASE...)  ║ MAX(VALNUM) ║
╠═══════════╬═══════════════╬═══════════════╬═════════════╣
║ 98945823  ║        06001  ║ Total         ║ 16.57       ║
║ 98945824  ║        06005  ║ Total         ║ 0.36        ║
╚═══════════╩═══════════════╩═══════════════╩═════════════╝

对于您的情况，请使用以下命令交换CTE硬编码值:

select vrec, valnum, valte from val_tb where
recd in (select recd from rectb where setd = 17)
AND (vid = 3 OR vid = 26 OR vid = 28);

您的数据结构非常差，因此此解决方案只是解决方法。您应该真正更改基础结构。

关于sql - Oracle将表从行转换为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34207857/