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
select ','+ [attributename]
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()'
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
from PLO_AS_EMP_FLOWPATH pf
where EMP_ID=u.EMP_ID and EMP_COMPID=u.COMPANY_ID
)Q order by LINEMANAGER_LEVEL
FOR XML PATH('')
It’s will be: