将多行 SQL 响应合并为一行并重命名值

Question

>我有一个查询，例如...

SELECT a.order_number, b.state
from tableone a
tabletwo b
WHERE
1=1
AND a.id = b.id
AND a.order_number = '12345';

这输出：

order_number    state
12345             CA
12345             AZ
12345             NY

我正在寻找而不是让它返回一个串联行，这样我就可以把这个结果保留为一行。

order_number       state
12345                 CA,AZ,NY

所以我构建了这个：

SELECT
a.order_number, 
LISTAGG(b.state, ',') WITHIN GROUP (ORDER BY a.order_number) AS states
from tableone a
tabletwo b
WHERE
1=1
AND a.id = b.id
AND a.order_number = '12345'
GROUP BY a.order_number;    

现在 - 我的问题是 - 如何为这些状态分配值？ 只有 3 个 - CA、AZ 和 NY，其中 CA = 1，AZ = 2，NY = 3。 我要么必须执行此逻辑才能与另一个使用这些数值来表示 SQL 中的状态的应用程序同步，或者在后端的另一个系统中，如果可能的话，我宁愿在这里执行此操作......

我希望我的输出看起来像这样：

order_number       state
12345                 1,2,3

Answer 1

如果您必须对转换进行硬编码，并且没有查找表将州代码转换为数字，则可以使用大小写表达式：

CASE b.state WHEN 'CA' THEN 1 WHEN 'AZ' THEN 2 WHEN 'NY' THEN 3 END

您可以在LISTAGG()调用中使用：

SELECT
a.order_number, 
LISTAGG(CASE b.state WHEN 'CA' THEN 1 WHEN 'AZ' THEN 2 WHEN 'NY' THEN 3 END, ',')
WITHIN GROUP (ORDER BY a.order_number) AS states
FROM tableone a
JOIN tabletwo b
ON a.id = b.id
WHERE
1=1
AND a.order_number = '12345'
GROUP BY a.order_number;    
ORDER_NUMBER STATES              
------------ --------------------
12345        1,2,3               

顺便说一下，我已经冒昧地切换到正确的连接语法。此外，order by子句目前并没有真正执行任何操作，因为您按该值进行分组，因此它在组中始终相同;如果顺序很重要，那么您需要更改它 - 可能更改为相同的大小写表达式。