Rows to Column Transformation in SQL Server

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 396 red flag
Rating: 5 out of 5  
 1 vote(s)

The objective of this article is to demonstrate the various ways of transforming Rows to Column in SQL Server


 Download source code for Rows to Column Transformation in SQL Server

Recommendation
Read A step by step approach to make a Photo Collage in AngularJS before this article.

Introduction

Let us consider that we have a table as under

The objective is to transform the table in such a way that, the output should resemble

Initial Setup

Let us first create the table and fill it up with some records

--Declare a table @T
DECLARE @T TABLE(
ID INT PRIMARY KEY IDENTITY (1,1)
,Product VARCHAR(50)
,Price DECIMAL(18,2)) 

--Insert some records to it
INSERT INTO @T VALUES('Milk',200),('Milk',500),('Milk',150),('Milk',278)
INSERT INTO @T VALUES('Butter',100),('Butter',500),('Butter',120),('Butter',145)
INSERT INTO @T VALUES('Sugar',600),('Sugar',250),('Sugar',279),('Sugar',199)

--Project the record
SELECT *
FROM @T

Solution 1: Using Pivoting

Our first solution will be using PIVOT as under

-- The transform query
SELECT
     PRODUCT
     ,[Price1]
     ,[Price2]
     ,[Price3]
     ,[Price4] 
FROM
    (
        SELECT 
            Product
            , Price
            ,Rn ='Price' + CAST(ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 1)) AS VARCHAR)
        FROM @T
    ) x
PIVOT
(
    MAX(Price) FOR Rn IN ([Price1], [Price2],[Price3], [Price4])
) p

We are first creating the Columns (Price1, Price2...Price 4) at run time using the ROW_NUMBER() function. In order to keep the values in the columns intact, we are sorting over a constant value (SELECT 1).Then inside the PIVOT function, we are using the MAX aggregated functions. MAX keeps the max values of the Price(s) column based on column values used in the max function.And finally we are projecting the transformed records.

Solution 2: Using Conditional Aggregation

Our second solution will be using Conditional Aggregation as under

SELECT 
        Product
       ,Price1 = MAX(CASE WHEN Rn = 1 THEN Price END)
       ,Price2 = MAX(CASE WHEN Rn = 2 THEN Price END)
       ,Price3 = MAX(CASE WHEN Rn = 3 THEN Price END)
       , Price4 = MAX(CASE WHEN Rn = 4 THEN Price END)
FROM (SELECT 
            t.*,
            Rn=ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 1))
      FROM @T t
     ) fr
GROUP BY Product

When we execute the inner query i.e.

SELECT 
            t.*,
            Rn=ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 1))
      FROM @T t

We receive the below output

As can be figure out that, by using the Row_Number() function, we are determining the sequence in which the prices are occurring for the Products. The next one is to just transform which we have done in the outer query. Basically, it's the way we used to perform PIVOTING before Sql Server 2005.

Solution 3: Using FOR XML PATH and PARSENAME function

In our third solution will be using FOR XML PATH and PARSENAME as under

;WITH Cte AS(
    
    SELECT 
          Product 
          ,Prices =
                    STUFF((SELECT ','+ CAST(CAST(Price AS INT) AS VARCHAR)
                    FROM @T T1
                    WHERE T1.Product=T2.Product
                    FOR XML PATH('')),1,1,'') 
FROM @T T2
GROUP BY T2.Product
)

SELECT 
        Product,
        Price1 = PARSENAME(REPLACE(Prices,',','.'),4),
        Price2 = PARSENAME(REPLACE(Prices,',','.'),3),
        Price3 = PARSENAME(REPLACE(Prices,',','.'),2),
        Price4 = PARSENAME(REPLACE(Prices,',','.'),1)
FROM Cte

The FOR XML PATH combines the rows with a comma separated value for each Product as shown under.

PARSENAME function helps to extract the values.

For all the three solutions provided above, the output is as under

Conclusion

In this article we have learnt how to transform Rows to Column by looking into 3 kind of solutions for the same problem. We have learnt the usage of PIVOT, For XML Path, ParseName, and also how we used to perform PIVOTING before it's advent. Hope this will be useful. Thanks for reading. Zipped file attached.

Recommendation
Read Pagination using EmberJS and WebAPI after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)