英文:
Listagg() with distinct and order by
问题
以下是您提供的翻译结果：
需要在Snowflake中获取以下结果的帮助
| 代码 | 描述 |
|---|---|
| 123 | 汽车 |
| 456 | 公共汽车 |
| 789 | 自行车 |
| 123 | 汽车 |
| 789 | 自行车 |
期望结果
| 代码 | 描述 |
|---|---|
| 123,456,789 | 汽车,公共汽车,自行车 |
尝试过以下方法：
Select
listagg(distinct Code,',') within group (order by Code ) as 代码,
listagg(distinct Description,',') within group (order by Description) as 描述
from 表名
但未按照预期顺序获取代码和描述。
英文:
Need help in getting below result in Snowflake
| Code | Description |
|---|---|
| 123 | CAR |
| 456 | BUS |
| 789 | CYCLE |
| 123 | CAR |
| 789 | CYCLE |
Expected Result
| Code | Description |
|---|---|
| 123,456,789 | CAR,BUS,CYCLE |
tried with:
Select
listagg(distinct Code,',') within group (order by Code ) as Code,
listagg(distinct Description,',') within group (order by Description) as Description
from table
But not getting code and description in respective order.
答案1
得分: 2
在`LISTAGG`内部，`Code`和`Description`都是独立排序的。为了将`Code`与相应的`Description`保持在一起，可以使用附加的排序列：
SELECT listagg(Code, ',') within group (order by rn) as Code,
listagg(Description, ',') within group (order by rn) as Description
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Code, Description) AS rn
FROM (SELECT DISTINCT Code, Description FROM tab)) AS sub;
对于源数据：
CREATE OR REPLACE TABLE tab(CODE INT, DESCRIPTION TEXT)
AS
SELECT 123 ,'CAR' UNION ALL
SELECT 456 ,'BUS' UNION ALL
SELECT 789 ,'CYCLE' UNION ALL
SELECT 123 ,'CAR' UNION ALL
SELECT 789 ,'CYCLE';
输出：
|CODE| DESCRIPTION|
|---|---|
|123,456,789 |CAR,BUS,CYCLE|
英文:
Both Code and Description are sorted independently inside LISTAGG. To keep Code with respective Description additional sort column could be used:
SELECT listagg(Code,',') within group (order by rn) as Code,
listagg(Description,',') within group (order by rn) as Description
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Code, Description) AS rn
FROM (SELECT DISTINCT Code, Description FROM tab)) AS sub;
For source data:
CREATE OR REPLACE TABLE tab(CODE INT, DESCRIPTION TEXT)
AS
SELECT 123 ,'CAR' UNION ALL
SELECT 456 ,'BUS' UNION ALL
SELECT 789 ,'CYCLE' UNION ALL
SELECT 123 ,'CAR' UNION ALL
SELECT 789 ,'CYCLE';
Output:
| CODE | DESCRIPTION |
|---|---|
| 123,456,789 | CAR,BUS,CYCLE |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论