Converting Comma Separated Value to Rows and Vice Versa in SQL Server

By Arshad Ali

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')

 

Result Screenshot

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

 

Result Screenshot

 

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

 

Result Screenshot

 

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.

Resources

CONCAT and STUFF Functions in SQL Server 2012

COALESCE function

CHARINDEX function

Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server

By Arshad Ali

Introduction

In my last article “Converting Comma Separated Value to Rows and Vice Versa in SQL Server”, I talked about how you can convert comma separated (or separated with some other character) values in a single column into rows and vice versa. In this article, I demonstrate how you can convert rows values into columns values (PIVOT) and columns values into rows values (UNPIVOT) in SQL Server.

Converting Rows to Columns – PIVOT

SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output. The basic syntax for a PIVOT relational operator looks like this:

SELECT <<ColumnNames>> 
FROM <<TableName>> 
PIVOT
 (
   AggregateFunction(<<ColumnToBeAggregated>>)
   FOR PivotColumn IN(<<PivotColumnValues>>)
 ) AS <<Alias>> 

With the script below, let’s create a table and load some data into it. As you can see in the image below, it has sales information for some countries for a couple of years. Also, if you notice, for each country and for each year there is a separate row.

CREATE TABLE [dbo].[PivotExample](
       [Country]   [nvarchar](50)   NULL,
       [Year]   [smallint] NOT NULL,
       [SalesAmount]   [money] NULL
)
GO
 
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2005, 1309047.1978)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2006, 521230.8475)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2007, 2838512.3550)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'France', 2008, 922179.0400)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2007, 3033784.2131)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'France', 2005, 180571.6920)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2006, 591586.8540)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2006, 621602.3823)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2005, 291590.5194)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2005, 1100549.4498)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2007, 535784.4624)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'France', 2007, 1026324.9692)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2007, 1058405.7305)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2006, 2154284.8835)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2008, 1210286.2700)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2008, 3324031.1600)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2008, 1076890.7700)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United Kingdom',   2007, 1298248.5675)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Australia', 2008, 2563884.2900)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2005, 146829.8074)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Germany', 2005, 237784.9902)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'Canada', 2008, 673628.2100)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'United States',   2006, 2126696.5460)
INSERT   [dbo].[PivotExample]([Country],   [Year], [SalesAmount])   VALUES (N'France', 2006, 514942.0131)
GO
 
SELECT * FROM [dbo].[PivotExample] ORDER   BY Country
GO

Results Screenshot

 

Now by using the PIVOT operator, we will convert row values into column values with the script given below and the results as shown in the image below.

Though we have used the SUM aggregation function, in this case there is no summation, as there is only one row for each unique combination for country and year. Please note the use of brackets for pivot column values; these are required.

SELECT   [Country], [2005],   [2006], [2007],   [2008]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN([2005], [2006], [2007], [2008])
) AS P

Results Screenshot

Bringing Dynamism to the PIVOT

If you notice in the above script, we have provided values (2005, 2006, 2007 and 2008) for pivot columns as these values are available in the original datasets. But what if some additional values are expected to come in the future, for example 2009 and 2010, etc. for the pivot column?

In that case, you can still use the pivot column values, which are expected to come (or which are still not available in the original dataset) in the future though you will see NULL for its values. The script below shows this scenario and the image below shows NULLs for the years 2009 and 2010 as there is no data for these years.

SELECT   [Country], [2005],   [2006], [2007],   [2008], [2009],   [2010]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN([2005], [2006], [2007], [2008], [2009], [2010])
) AS P

Results Screenshot

The above discussed approach works fine if you already know all of the possible values for the pivot column, but what if you don’t?

In that case, you can write a dynamic query to first grab all the unique values for the pivot column at runtime and then a write dynamic query to execute it with the pivot query as shown below:

--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
 
--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(Year)
FROM (SELECT DISTINCT Year FROM [dbo].[PivotExample]) AS PivotExample
 
SELECT   @PivotColumns
 
--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT Country, ' +   @PivotColumns + '
    FROM [dbo].[PivotExample] 
    PIVOT( SUM(SalesAmount) 
          FOR Year IN (' + @PivotColumns + ')) AS P'
 
SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql@SQLQuery

 

Results Screenshot

Converting Columns to Rows – UNPIVOT

UNPIVOT is another relational operator in SQL Server that performs almost the reverse operation of PIVOT, by rotating column values into rows values. Let me demonstrate this with an example; lets create a table with pivoted data from the previous query with the script below. The image below shows data of the newly created table with pivoted data.

SELECT   [Country], [2005],   [2006], [2007],   [2008] 
INTO   [dbo].[UnpivotExample]
FROM   [dbo].[PivotExample] 
PIVOT
(
       SUM(SalesAmount)
       FOR [Year] IN([2005], [2006], [2007], [2008])
) AS P
GO
SELECT * FROM [dbo].[UnpivotExample] ORDER   BY Country
GO

Results Screenshot

To rotate column values into row values, we will now use the UNPIVOT operator as shown below. The image below shows rotated data:

SELECT   Country, Year, SalesAmount 
FROM [dbo].[UnpivotExample]
UNPIVOT
(
       SalesAmount
       FOR [Year] IN([2005], [2006], [2007], [2008])
) AS P

Results Screenshot

If you refer back, I said UNPIVOT is almost the reverse of the PIVOT operator; this means it might or might not be exactly the same. The reason is, Pivot performs aggregation while rotating row values into column values and might merge possible multiple row values into single column value in the output. For example, consider for a given country and year there are two values, say 5000 and 6000. Now when you pivot it, the output will have 11000 as the column value if you have SUM as the aggregation function. Later if you want to unpivot it back, you will get 11000 not the bi-furcated values (5000 and 6000 as original). Having said that, we can say if the pivoted values are aggregated values, it will not be possible to get the original data back.

Conclusion

In this article, I demonstrated how you can convert row values into column values (PIVOT) and column values into row values (UNPIVOT) in SQL Server. I also talked about writing a dynamic query to write a dynamic pivot query in which possible pivot column values are determined at runtime.

Resources