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.
No comments:
Post a Comment