select merged column value sql

Question

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

Try adding a function that when a booking is cancelled, then created, the two previous notes are deleted, and a new note with both appears. You might have to do this manually. — Garlic Bread Express, Apr 16 '23 at 16:07

score 1 · Answer 1 · answered Apr 16 '23 at 16:06

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.

score 0 · Answer 2 · answered Apr 16 '23 at 16:07

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

select merged column value sql

2 Answers2