英文:
select merged column value sql
问题
以下是翻译好的部分：
Table Bookings 包含 BookingId 和其他与预订相关的列。
每当预订有更新时，会将备注添加到 BookingNote 表中。
BookingNote 表示例：
| BookingId | Note |
|---|---|
| 123 | 预订已创建 |
| 123 | 用户详细信息已更新 |
| 123 | 预订已取消 |
结果
| BookingId | Note |
|---|---|
| 123 | 预订已取消 |
| 123 | 预订已创建 |
如何在 BookingId 的情况下获取合并的备注，以便我可以获得以下结果：
| BookingId | Note |
|---|---|
| 123 | 预订已取消，预订已创建 |
英文:
There is 2 tables Bookings and BookingNote
Table Bookings contains BookingId and other booking related columns.
Whenever there is an update to the booking, A note is added to the BookingNote Table.
BookingNote table example:
| BookingId | Note |
|---|---|
| 123 | Booking Created |
| 123 | User Details Updated |
| 123 | Booking Cancelled |
Select B.BookingId ,
Case
When N.Note = 'Booking Created' Then N.Note
When N.Note = 'Booking Cancelled' Then N.Note
End
From Bookings as B
Join Notes as N
On B.BookingId = N.BookingId
Where N.Note = 'Booking Created'
Or N.Note = 'Booking Cancelled'
Result
| BookingId | Note |
|---|---|
| 123 | Booking Cancelled |
| 123 | Booking Created |
How can I get a merged note when there is a 'Booking Created' and 'Booking Cancelled' for a BookingId so that I can get a result like this
| BookingId | Note |
|---|---|
| 123 | Booking Cancelled , Booking Created |
答案1
得分: 1
这可以通过使用 STRING_AGG 来实现：
SELECT BookingId, STRING_AGG(Note, ', ') FROM BookingNote GROUP BY BookingId;
这通过指定表中的公共标识符（在本例中为 BookingId），然后使用 STRING_AGG 连接该标识符的所有文本 - 在本例中由逗号分隔，如指定的那样。
英文:
This can be accomplished using STRING_AGG:
SELECT BookingId, STRING_AGG (Note,', ') FROM BookingNote group by BookingId;
This works by specifying the common identifier in the table (in this case, BookingId), and then STRING_AGG joins all text common to that identifier - in this case separated by a comma as specified.
答案2
得分: 0
根据您使用的数据库不同，您可以使用字符串聚合和按BookingID分组的方法。
例如，如果您正在使用MSSQL（T-SQL）：
Select B.BookingId,
STRING_AGG(CONVERT(NVARCHAR(max),
Case
When N.Note = 'Booking Created' Then N.Note
When N.Note = 'Booking Cancelled' Then N.Note
End, ',')
From Bookings as B
Join Notes as N
On B.BookingId = N.BookingId
Where N.Note = 'Booking Created'
Or N.Note = 'Booking Cancelled'
GROUP BY B.BookingId
(Note: This is a translation of the provided code snippet without additional content.)
英文:
Depending on the database you are using, you can use string aggregation and group by BookingID.
E.g. if you are using MSSQL (T-SQL):
Select B.BookingId ,
STRING_AGG(CONVERT(NVARCHAR(max),
Case
When N.Note = 'Booking Created' Then N.Note
When N.Note = 'Booking Cancelled' Then N.Note
End , ',')
From Bookings as B
Join Notes as N
On B.BookingId = N.BookingId
Where N.Note = 'Booking Created'
Or N.Note = 'Booking Cancelled'
GROUP BY B.BookingId
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论