英文:
Transpose column to row without any 'id' or key column based on row number
问题
抱歉，您提供的内容已经是英文的数据库查询问题，无需进一步翻译。如果您需要关于这个查询问题的帮助或解决方案，请随时向我提问。
英文:
Hi I have table like this
| name | cat_type |
|---|---|
| coco | Dom |
| coky | Anggora |
| chee | Turk |
| eena | Persian |
| onaa | Dom |
| haina | Turk |
| haani | Dom |
I want to try to transform to this
| Dom | Anggora | Turk | Persian |
|---|---|---|---|
| coco | coky | chee | eena |
| onaa | null | haina | null |
| haani | null | null | null |
I have tried the following approach:
SELECT *
FROM my_table
PIVOT( STRING_AGG(name) FOR cat_type IN ('Dom','Anggora','Turk','Persian'))
But I didn't get what I want, as I got result like the following:
| Dom | Anggora | Turk | Persian |
|---|---|---|---|
| coco,onaa,haani | coky | chee,haina | eena |
Is there something wrong in my query?
Thank you in advance!
答案1
得分: 0
这个问题看起来像是经典的数据透视表的一个略微变种。你所遗漏的是你正在分组的列，而在你的情况下，它是行号。
WITH cte AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY cat_type ORDER BY name) AS rn
FROM my_table
)
SELECT *
FROM cte
PIVOT (STRING_AGG(name) FOR cat_type IN ('Dom','Anggora','Turk','Persian'))
英文:
This problem looks like a slightly variation of classical pivot. What you're missing is the column you're grouping by, which in your case is the row number.
WITH cte AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY cat_type ORDER BY name) AS rn
FROM my_table
)
SELECT *,
FROM cte
PIVOT (STRING_AGG(name) FOR cat_type IN ('Dom','Anggora','Turk','Persian'))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论