Easiest way to Combine multiple rows in single row

I want to discuss about Combine multiple rows in single row.

Combine multiple rows in single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by:

The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results. The SQL Server T-SQL commands used are STUFF and FOR XML.
The T-SQL STUFF command is used to concatenate the results together. In this example, the results are separated by a semi-colon.
The FOR XML option for the SELECT command has four options (i.e. RAW, AUTO, EXPLICIT or PATH) to return the results. In this example, the PATH parameter is used to retrieve the results as an XML string.

SQL Server STUFF() Function:

Before going to the examples, we need to understand the workings of the commands mentioned above. The STUFF() function puts a string in another string, from an initial position. With this we can insert, replace or remove one or more characters.

This syntax is STUFF(character_expression, start, length, replaceWith_expression):

character_expression: string to be manipulated
start: initial position to start
length: number of characters to be manipulated
replaceWith_expression: characters to be used

Use Stuff:

select ProgramId,
stuff(
(
    select ','+ [attributename]
    from Table1 
    where programid = t.programid for XML path('')

),1,1,'') as AttributeNames
from (select distinct programid 
      from Table1 )t

select ProgramId,

stuff(

(

select ','+ [attributename]

from Table1

where programid = t.programid for XML path('')

),1,1,'') as AttributeNames

from (select distinct programid

from Table1 )t

You can Try as Bellow:

select EmpPath + ',' AS 'data()'  
from 
(
select LINEMANAGER_LEVEL,(
   select  '('+u.PR_CODE+')'+ u.EMP_NAME +' ' + u.DESIGNATION_NAME
   from REQUSITION_USER u
   where u.COMPANY_ID = LINEMANAGER_COMPID and u.EMP_ID = LINEMANAGER_ID
) EmpPath
from PLO_AS_EMP_FLOWPATH pf
where EMP_ID=u.EMP_ID and EMP_COMPID=u.COMPANY_ID 
and LINEMANAGER_LEVEL!=0
)Q order by LINEMANAGER_LEVEL
FOR XML PATH('')

select EmpPath + ',' AS 'data()'

from

(

select LINEMANAGER_LEVEL,(

select '('+u.PR_CODE+')'+ u.EMP_NAME +' ' + u.DESIGNATION_NAME

from REQUSITION_USER u

where u.COMPANY_ID = LINEMANAGER_COMPID and u.EMP_ID = LINEMANAGER_ID

) EmpPath

from PLO_AS_EMP_FLOWPATH pf

where EMP_ID=u.EMP_ID and EMP_COMPID=u.COMPANY_ID

and LINEMANAGER_LEVEL!=0

)Q order by LINEMANAGER_LEVEL

FOR XML PATH('')

It’s will be:

3 thoughts on “SQL Server: Combine multiple rows in single row”

kala tranter says:

May 20, 2019 at 10:46 pm

It’s actually a cool and helpful piece of information. I’m glad that
you just shared this helpful information with us.
Please keep us informed like this. Thanks for sharing.

DalanoSymn says:

December 14, 2019 at 3:20 pm

Excellent ..
Superb .. I’ll bookmark your website and take the feeds additionally?

FletcherPhothirath says:

December 16, 2019 at 1:21 am

I’m glad to find so many useful info right.
thanks for sharing.