Display Multiple Rows as a single Line, Sql Server

Display Multiple Rows as a single Line

DECLARE @Temp1 TABLE
    (	UserName	VARCHAR(MAX),
    	MessageId	INT,
    	[Message]	VARCHAR(MAX)
    )
    
    --Insert Data T1
    INSERT INTO @Temp1( UserName, MessageId, Message )
    VALUES  ( 'U1','1','First'  )
    INSERT INTO @Temp1( UserName, MessageId, Message )
    VALUES  ( 'U2','2','Hi'  )
    INSERT INTO @Temp1( UserName, MessageId, Message )
    VALUES  ( 'U1','3','hello'  )
    INSERT INTO @Temp1( UserName, MessageId, Message )
    VALUES  ( 'U4','4','temp'  )
    
    
    DECLARE @Temp2 TABLE
    (	UserName	VARCHAR(MAX),
    	UnitName	VARCHAR(MAX)
    )
    
    --Insert Data T2
    INSERT INTO @Temp2( UserName, UnitName )
    VALUES  ( 'U1', 'Unit1' )
    INSERT INTO @Temp2( UserName, UnitName )
    VALUES  ( 'U1', 'Unit2' )
    INSERT INTO @Temp2( UserName, UnitName )
    VALUES  ( 'U2', 'Unit3' )
    
    SELECT DISTINCT T1.UserName,T1.MessageId ,
                (
                    SELECT T2.UnitName + ',' AS [text()]
                    FROM @Temp2 T2
                    WHERE T2.UserName = T1.UserName
                    ORDER BY T2.UserName
                    FOR XML PATH ('')
                ) [Units]
    FROM @Temp1 T1
    ORDER BY T1.MessageId ASC


run \| edit \| history \| help	0