SQL Server select merged column value sql [duplicate]

m4pnthwp 于 2023-04-19 发布在其他

关注(0)|答案(2)|浏览(110)

This question already has answers here:

How to concatenate text from multiple rows into a single text string in SQL Server (47 answers)
Closed 2 days ago.

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

sql-server

来源：https://stackoverflow.com/questions/76028829/select-merged-column-value-sql

2条答案

按热度按时间

uxhixvfz1#

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.

赞(0）回复(0）举报 2023-04-19

ohfgkhjo2#

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

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server select merged column value sql [duplicate]

2条答案

相关问题

热门标签

最新问答