Introduction
Often while reporting you will encounter a situation where you will have comma separated (or separated with some other character) values in a single column but you want to report them in rows whereas in some other cases you might have values in multiple rows and want them to be a single value separated by comma or some other character. In this article, I am going to demonstrate how you can write queries in SQL Server to handle these scenarios quickly.
Converting Comma Separated Value to Rows
For converting a comma separated value to rows, I have written a user defined function to return a table with values in rows. It takes comma separated values as the input parameter, iterates through it as long as it finds a comma in the value, takes each value before the comma, inserts into a table and finally returns the inserted data from that table.
It makes use of CHARINDEX inbuilt function to search for the existence of commas in the input parameter and returns the starting 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 (which has already been inserted into table) 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. It also uses LTRIM and RTRIM functions to remove any extra spaces from the beginning or end of the value if there are any.
CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString varchar(1000)) RETURNS @Result TABLE (Column1 VARCHAR(100)) AS BEGIN DECLARE @IntLocation INT WHILE (CHARINDEX(',', @CommadelimitedString, 0) > 0) BEGIN SET @IntLocation = CHARINDEX(',', @CommadelimitedString, 0) INSERT INTO @Result (Column1) --LTRIM and RTRIM to ensure blank spaces are removed SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString, 0, @IntLocation))) SET @CommadelimitedString = STUFF(@CommadelimitedString, 1, @IntLocation, '') END INSERT INTO @Result (Column1) SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed RETURN END GO --Using the UDF to convert comma separated values into rows SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange') SELECT * FROM dbo.BreakStringIntoRows('Apple , Banana, Orange')
Converting Rows to Comma Separated Values
Before I go into detail and demonstrate converting rows into comma separated values, let’s first create a table and load some data with the script given below and as shown in the image.
CREATE TABLE Fruits ( Name VARCHAR(50) ) GO INSERT Fruits(Name) VALUES ('Apple') INSERT Fruits(Name) VALUES ('Banana') INSERT Fruits(Name) VALUES ('Orange') GO SELECT * FROM Fruits GO
Now we have values in rows and to convert them to a single comma separated value, you can use the script below, which uses the COALESCE inbuilt function.
DECLARE @ConcatString VARCHAR(4000) SELECT @ConcatString = COALESCE(@ConcatString + ', ', '') + Name FROM Fruits SELECT @ConcatString AS Fruits GO
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 the above example, I have considered the comma as a character to separate values; though you can modify the above script to have any other character like $ or |, etc. for separating values.
Conclusion
In this article, I talked about how you can write a query using inbuilt SQL Server functions to convert comma separated values into rows or convert multiple rows values to be a single value separated by a comma. I have demonstrated using a comma for separating values but you can use any other character for value separation.
