Rows to Column Transformation in SQL Server

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.