代码之家 › 专栏 › 技术社区 › SASPYTHON

透视技巧oraclepl/SQL,如何透视?

pivot oracle sql

1

SASPYTHON · 技术社区 · 8 年前

我的密码是

SELECT COUNT(DISTINCT cr.report_id) report_id, cr.contact_type'
FROM contact_report cr
WHERE cr.contact_type IN ('A','P','Y','B')
GROUP BY cr.contact_type

它显示了

report_id cr.contact_type
2         P
4         A
1         B

然后我用枢轴

  SELECT * FROM
                    (
                    SELECT COUNT(DISTINCT cr.report_id) report_id, cr.contact_type
                    FROM contact_report cr
                    WHERE cr.contact_type IN ('A','P','Y','B')
                    GROUP BY cr.contact_type
                    )
                    PIVOT
                    (SUM(report_id) FOR contact_type IN (
                                  'A' 
                                  'P' 
                                  'Y' 
                                  'B'

我离我想要的东西很近

 A   P   Y   B
 4   2       1

问题1

Y显示为null,如何对nvl(,0)进行编码以在透视表中将Y显示为0。

非常感谢

2 回复 | 直到 6 年前

1

tbone 8 年前

您可以尝试:

with dat as (
    select 2 as report_id,'P' as contact_type from dual
    union all
    select 4 as report_id,'A' as contact_type from dual
    union all
    select 1 as report_id,'B' as contact_type from dual
)
select nvl(A,0) as A, nvl(B,0) as B, nvl(P,0) as P, nvl(Y,0) as Y
from (
  select report_id, contact_type from dat
)
PIVOT
(
  max(report_id) for contact_type in ('A'as"A",'B'as"B",'P'as"P",'Y'as"Y")
);

输出:

A   B   P   Y
4   1   2   0

2

0

Alex Poole 8 年前

IN (...) 子句可以在主选择列表中显式引用它们,并应用 nvl 或 coalesce

SELECT COALESCE(a, 0) as a,
  COALESCE(p, 0) as p,
  COALESCE(y, 0) as y,
  COALESCE(b, 0) as b
FROM (
  SELECT COUNT(DISTINCT cr.report_id) report_id, cr.contact_type
  FROM contact_report cr
  WHERE cr.contact_type IN ('A','P','Y','B')
  GROUP BY cr.contact_type
)
PIVOT (
  SUM(report_id)
  FOR contact_type IN (
    'A' as a, 
    'P' as p, 
    'Y' as y, 
    'B' as b
  )
);

         A          P          Y          B
---------- ---------- ---------- ----------
         4          2          0          1

嗯,你不知道有

SELECT COALESCE("'A'", 0) as a,
  COALESCE("'P'", 0) as p,
  COALESCE("'Y'", 0) as y,
  COALESCE("'B'", 0) as b
FROM (
...

有点难相处。

在这种情况下,你是否使用 sum 或 max ,但我认为这是比较常见的搭配 最大值 .