ORACLE SQL：如何在 Pivot 函数中用 0 替换 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38234996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE SQL: How do I replace NULL with 0 in a Pivot function
提问by user3644952
How can I replace NULL with 0 in a PIVOT function on ORACLE SQL? This is the query I'm trying to write:
如何在 ORACLE SQL 的 PIVOT 函数中用 0 替换 NULL？这是我正在尝试编写的查询：
SELECT *
FROM
(
SELECT DISTINCT
CUSTOMER_ID AS CUSTOMER_ID,
CASE
WHEN CATEGORY_CODE = '01' THEN 'CAT 01'
WHEN CATEGORY_CODE = '02' THEN 'CAT 02'
WHEN CATEGORY_CODE = '03' THEN 'CAT 03'
ELSE 'OTHER' END AS CATEGORY,
SUM(ORDERS) AS ORDERS
FROM
TABLE_01
GROUP BY
CUSTOMER_ID,
CASE
WHEN CATEGORY_CODE = '01' THEN 'CAT_01'
WHEN CATEGORY_CODE = '02' THEN 'CAT_02'
WHEN CATEGORY_CODE = '03' THEN 'CAT_03'
ELSE 'OTHER' END
)
PIVOT
(
SUM(ORDERS)
FOR CATEGORY IN
(
'CAT_01',
'CAT_02',
'CAT_03',
'OTHER'
)
)
)
;
What I would like is to have a table that when a customer doesn't have any order on a specific category, it would return 0 instead of NULL. like this:
我想要的是有一个表，当客户在特定类别上没有任何订单时，它会返回 0 而不是 NULL。像这样：
CUSTOMER_ID CAT_01 CAT_02 CAT_03
00001 0 100 0
00002 100 0 0
00003 0 0 100
Please, keep in mind that this is a very simplified part of a complex query with several categories and nested queries.
请记住，这是具有多个类别和嵌套查询的复杂查询的非常简化的部分。
回答by sstan
You'll have to change the select *part of your query at the top to specify the columns individually, so that you can wrap them in calls to nvl. You can also use coalesceif you like.
您必须更改select *顶部的查询部分以单独指定列，以便您可以将它们包装在对nvl. coalesce如果你喜欢，你也可以使用。
select customer_id,
nvl(cat_01, 0) as cat_01,
nvl(cat_02, 0) as cat_02,
nvl(cat_03, 0) as cat_03,
nvl(other, 0) as other
from (...
回答by Pradeep Dubey
SELECT CUSTOMER_ID,
NVL(CAT_01,0),
NVL(CAT_02,0)
NVL( CAT_03
FROM
(
SELECT DISTINCT
CUSTOMER_ID AS CUSTOMER_ID,
CASE
WHEN CATEGORY_CODE = '01' THEN 'CAT 01'
WHEN CATEGORY_CODE = '02' THEN 'CAT 02'
WHEN CATEGORY_CODE = '03' THEN 'CAT 03'
ELSE 'OTHER' END AS CATEGORY,
SUM(ORDERS) AS ORDERS
FROM
TABLE_01
GROUP BY
CUSTOMER_ID,
CASE
WHEN CATEGORY_CODE = '01' THEN 'CAT_01'
WHEN CATEGORY_CODE = '02' THEN 'CAT_02'
WHEN CATEGORY_CODE = '03' THEN 'CAT_03'
ELSE 'OTHER' END
)
PIVOT
(
SUM(ORDERS)
FOR CATEGORY IN
(
'CAT_01',
'CAT_02',
'CAT_03',
'OTHER'
)
)
)
;
