sql - Oracle 将表从行转换为列

Question

我有一个疑问:

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 执行此操作？

Answer 1

区分 valte 的一种方法values 可以检查字符串是否只包含数字(糟糕的解决方案，但应该有效):

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);

你的数据结构很差，所以这个解决方案只是解决方法。你真的应该改变底层结构。