英文:
How to combine multiple rows of text in SQL
问题
我有一个非常大的SQL数据库，它将文本字段存储为多行，并带有一个序列号。我需要找到一种编写SQL代码的方法，将所有这些序列号的注释组合成一个连贯的注释字段，以供视图使用。
我在SQL方面相对新手，所以实际上不太清楚从哪里开始合并这些内容。在其他编程语言中，我会编写一个迭代循环，但我不太确定如何在SQL中实现类似的操作。
英文:
I have a VERY large SQL database that stores a text field into multiple rows with a sequence number. I need to find a way to write SQL code to combine all of those sequence notes into one cohesive notes field for a view.
Data source resembles this: Source
Desired output resembles this: Output
I'm relatively new to SQL so I'm honestly not sure where to begin with combining this. In other languages I'd write an iterative loop, but I'm not sure how to make anything like that happen in SQL.
答案1
得分: 0
DB语法可能会有所不同，但通常标准的SQL函数 LISTAGG 可以实现你想要的功能。
在你的情况下，你需要类似这样的语句：
select date, icn, count(sequence) as "Sequence Count",
listagg(notes, ' ') within group (order by sequence) as notes
from table
group by date, icn;
如果可能出现同一ICN条目对应多个日期的情况，那么你可能需要按icn进行分组，并对日期列应用聚合函数（例如 max）。
LISTAGG 通常有长度限制，并非所有的DB都支持它，因此你可能需要指定你所使用的具体数据库以获得更全面的答案。
另外，一个需要注意的一般性建议是，每当你想要使用迭代循环时，通常在SQL中意味着需要使用 group by 并应用聚合或窗口函数。
英文:
DB syntax can vary, but in general the standard SQL function LISTAGG does what you want.
In your case, you want something like
select date, icn, count(sequence) as "Sequence Count",
listagg(notes, ' ') within group (order by sequence) as notes
from table
group by date, icn;
If it's possible that you can have multiple dates for the same ICN entry, then you might need to group by icn and apply an aggregate function (e.g. max) to the date column.
LISTAGG often has length limits, and not all DBs support it, so you'll probably need to tag your specific database to get a more comprehensive answer.
Also a general note to look into - whenever you would want to use an iterative loop, usually in SQL that means a group by and an aggregate or window function.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论