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:
1 2 3 4 5 6 7 8 9 10 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
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.
Excellent ..
Superb .. I’ll bookmark your website and take the feeds additionally?
I’m glad to find so many useful info right.
thanks for sharing.