There are situations when we want the data to be presented in the comma separated values rather than individual rows. For e.g. see the data in the pic given below:
The above shows all Enames with its deptno. But the employees for each deptno is represented as an individual row. Now if we want to display the same data with comma separated values given in the screenshot below:
In order to achieve this, we can use the XML Path function in SQL Server. This will help in generating the individual values and we can separate individual values with the help of comma or any other identifier. The query to generate the above resultset is:
In the above query we have used substring function so as to remove the "," which comes at the start of every name.
The above is a wonderful tool to rearrange the values in the form of comma separated values.
The above shows all Enames with its deptno. But the employees for each deptno is represented as an individual row. Now if we want to display the same data with comma separated values given in the screenshot below:
The above is a wonderful tool to rearrange the values in the form of comma separated values.
Comments
sir, i have tried it using dynamic sql, but i think it endangers the database due to problems like sql injection. i think the way u suggested in the post, can help me. but how to implement it in a stored procedure?
You can pass in the form of string in a procedure or function with some identifier to break the text in the logic. Otherwise you can also use the xml passing/table passing in the function to achieve the same.