英文:
Need help creating a query to extract values from a single column and arrange them into individual columns in Oracle PL/SQL SQL
问题
以下是翻译好的内容：
我正在为一个数据提取编写一份提取报告，接收系统需要将单列中的值排列在各自的列中。
以下是数据的存储方式：
| 个人ID | 爱好 |
|---|---|
| 123 | 园艺 |
| 123 | 徒步旅行 |
| 176 | 阅读 |
| 366 | 徒步旅行 |
| 366 | 园艺 |
| 366 | 足球 |
以下是我需要查询生成的内容。请注意，我需要这个查询以每晚运行以提取数据的形式。还请注意，爱好需要按升序排序，以便每个人的每一列可能会有不同的值，如下所示：
| 个人ID | 爱好1 | 爱好2 | 爱好3 | 爱好4 | 爱好5 | 爱好6 |
|---|---|---|---|---|---|---|
| 123 | 园艺 | 徒步旅行 | ||||
| 176 | 阅读 | |||||
| 366 | 足球 | 园艺 | 徒步旅行 |
这就像是listagg的反向操作，我陷入了困境。如果有帮助，将不胜感激！
我尝试对爱好进行排序并返回第一行，但我不知道如何获取第2-6行。
在大家因为我再次提出数据透视表的问题而责怪我的之前，请注意，这不是对值求和，并且列没有被定义（园艺是是/否，徒步旅行是是/否，等等），因此我以前使用的数据透视表代码在这种情况下不起作用，除非我对此有错误的理解？
英文:
I'm writing an extract for a feed and the receiving system wants the values from a single column arranged in individual columns.
Here is how the data is stored:
| Person ID | Hobbies |
|---|---|
| 123 | Gardening |
| 123 | Hiking |
| 176 | Reading |
| 366 | Hiking |
| 366 | Gardening |
| 366 | Football |
And here is what I need the query to produce. Note that I need this to be in the form of a query that can run every night to extract the feed. Also note that the hobbies need to be sorted asc so a different value might go into each column for each person, as below:
| Person ID | Hobby 1 | Hobby 2 | Hobby 3 | Hobby 4 | Hobby 5 | Hobby 6 |
|---|---|---|---|---|---|---|
| 123 | Gardening | Hiking | ||||
| 176 | Reading | |||||
| 366 | Football | Gardening | Hiking |
It's like the opposite of listagg and I'm stuck. Any help would be much appreciated!
I tried sorting the hobbies and returning the first row, but I don't know how to get rows 2-6.
Before everyone jumps on me for asking yet another pivot table question, this is not summing values and the columns are not defined (Gardening y/n, Hiking y/n, etc.) so the pivot table code I've used before isn't working for this case - unless I'm thinking about this the wrong way??
答案1
得分: 3
我只会翻译代码部分，以下是翻译好的内容：
我只会翻译代码部分，以下是翻译好的内容：
select
person,
max(case when rn = 1 then hobby else null end) as hobby1,
max(case when rn = 2 then hobby else null end) as hobby2,
max(case when rn = 3 then hobby else null end) as hobby3
from
(
select
person,
hobby,
row_number() over (partition by person order by hobby) as rn
from foo
) t
group by person;
英文:
I would just derive a row number for each, and then use conditional aggregation.
select
person,
max(case when rn = 1 then hobby else null end) as hobby1,
max(case when rn = 2 then hobby else null end) as hobby2,
max(case when rn = 3 then hobby else null end) as hobby3
from
(
select
person,
hobby,
row_number() over (partition by person order by hobby) as rn
from foo
) t
group by person;
答案2
得分: 0
这是使用 PIVOT 进行操作的另一种方式：
with cte as (
select person, hobby,
row_number() over (partition by person order by hobby) as rn
from mytable
)
select *
from cte
pivot
( max(HOBBY)
for rn in (1 as "hobby1", 2 as "hobby2", 3 as "hobby3", 4 as "hobby4", 5 as "hobby5", 6 as "hobby6")
)
结果：
PERSON hobby1 hobby2 hobby3 hobby4 hobby5 hobby6
123 Gardening Hiking null null null null
176 Reading null null null null null
345 Football Gardening Hiking null null null
英文:
This is an other way to do it using PIVOT :
with cte as (
select person, hobby,
row_number() over (partition by person order by hobby) as rn
from mytable
)
select *
from cte
pivot
( max(HOBBY)
for rn in (1 as "hobby1", 2 as "hobby2", 3 as "hobby3", 4 as "hobby4", 5 as "hobby5", 6 as "hobby6")
)
Result :
PERSON hobby1 hobby2 hobby3 hobby4 hobby5 hobby6
123 Gardening Hiking null null null null
176 Reading null null null null null
345 Football Gardening Hiking null null null
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论