Oracle Pivot options

Question

>我有下面的枢轴和输出。我想显示以下内容。

1. 删除列周围的括号？
2. 添加 X 和 Null 的指示符来代替 1 和 0？

.SQL：

SELECT DISTINCT
*
FROM (
SELECT D.ID, D.DI, A.ID
FROM  A
LEFT JOIN  AD                  ON A.ID = AD.ID
LEFT JOIN  D                        ON AD.ID = D.ID
WHERE 1=1
AND A.ID = 890929
)
PIVOT
(
COUNT(ID) 
FOR DI IN  ( 'Low med','Soft','Regular','High Med','Other')
)

查询输出：

ID  'Low med' 'Soft' 'Regular'  'High Med'  'Other'
1      1         1      0           0          1

预期产出：

ID  LOW_MED    SOFT REGULAR        HIGH_MED       OTHER
1      X         X      NULL           NULL          X

Answer 1

您可以通过为透视表达式添加别名来删除单引号(不是括号，括号()(：

FOR DI IN ('Low med' as low_med, 'Soft' as soft, 'Regular' as regular,
'High Med' as high_med,'Other' as other)

然后，您可以将这些别名用于第二部分，但将大小写表达式添加到主查询中：

SELECT id,
case when low_med = 1 then 'X' else null end as low_med,
case when soft = 1 then 'X' else null end as soft,
case when regular = 1 then 'X' else null end as regular,
case when high_med = 1 then 'X' else null end as high_med,
case when other = 1 then 'X' else null end as other
FROM (
SELECT D.ID, D.DI, A.ID
FROM  A
LEFT JOIN  AD ON A.ID = AD.ID
LEFT JOIN  D ON AD.ID = D.ID
WHERE 1=1
AND A.ID = 890929
)
PIVOT
(
COUNT(ID) 
FOR DI IN ('Low med' as low_med, 'Soft' as soft, 'Regular' as regular,
'High Med' as high_med,'Other' as other)
)