How we can convert Comma Separated Value to Rows and Vice Versa?
Basic fundamental
While we are working on reporting some circumstances comes when we have to deal with comma separated values or any other characters in single column but we need those values in rows or separated with multiple columns. Also some time we need to convert rows into comma separated values. Now we will discuss on conversion of comma separated values to rows then we will discuss how we can create comma separated values from rows.
Sample: - “abcd, def, ghi, jkl, mno, xyz” these point to one column values.
Converting Comma Separated Value to Rows
We will use stuff function, charindex, ltrim, rtrim,substring
Steps we follow
- 1. We will create function, which take column values as parameter.
- 2. Iterate internally till end of the last values separated with special characters.
- 3. Insert each values to table variable
- 4. Then select inserted values from table as output of stored procedure.
It makes use of CHARINDEX inbuilt function to search for the existence of commas in the input parameter and returns the first position. It does that as long as the position is greater than zero. Then it makes use of the STUFF inbuilt function to replace a part of the main input parameter with a zero length string; effectively removing the value before the comma, which has already been extracted from the main input parameter and inserted into the table. We will use LTRIM and RTRIM functions to remove any extra spaces from the beginning or end of the value if there are any.
Now we will discuss how we can create rows into comma separated values.
For achieving desired output from rows to comma separated values we will use COALESCE FUNCTION. The COALESCE function takes a list of parameters, separated by commas, evaluates them and returns the value of the first of its input parameters that is not NULL.
Though COALESCE and ISNULL functions have a similar purpose, they can behave differently. For example, ISNULL function is evaluated only once whereas the input values for the COALESCE function can be evaluated multiple times or until it reaches to the first not-NULL value to return.
In example, I have considered the comma as a character to separate values.
you can created function which will concatenate rows separated with user defined special character.
