concatenate in sql only one column

Question

booking No  Movie Name      Guest Name
---------------------------------------------
643042      Horrer Movie    MARLON HARRIS
643042      Horrer Movie    ALICE HARRIS

I want to display this record as below in SQL Server

643042  Horrer Movie    ALICE HARRIS / MARLON HARRIS

Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Dan Def, Mar 12 '18 at 08:27
I am joining multiple table and i want First Name last name and middel name as comma seprated — Tejesvi Tejesvi, Mar 12 '18 at 08:29

score 0 · Answer 1 · answered Mar 12 '18 at 08:29

Try somthing like this:

select t.booking_no,
       t.Movie_name,
        stuff( (    select ' / ' + t2.Guest_Name
                    from table_name t2
                    where t2.booking_no = t.booking_no
                    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                    ,1,3,'')  as Guest_Name

from table_name t
group by t.booking_no, t.Movie_name

score 0 · Answer 2 · answered Mar 12 '18 at 08:32

As said in the comments, use FOR XML PATH

    ;WITH T ([booking No] , [Movie Name] ,     [GuestName])
    AS
    (
    SELECT 643042   ,   'Horrer Movie',    'MARLON HARRIS'
    UNION ALL 
    SELECT 643042   ,   'Horrer Movie',    'ALICE HARRIS'
    )

    SELECT [booking No] , [Movie Name] ,
        GuestNames = LTRIM(RTRIM(STUFF(CAST((SELECT ' / '+ B.[GuestName]
        FROM T B
        WHERE a.[booking No] = b.[booking No]
        FOR XML PATH (''),TYPE) AS NVARCHAR(1000)),1,2,''))) 
    FROM T A
    GROUP BY [booking No] , [Movie Name]

concatenate in sql only one column

2 Answers2