# Rows to Column Transformation in SQL Server Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1289 Rating: 5 out of 5
1 vote(s) Download source code for Rows to Column Transformation in SQL Server

Recommendation

## 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
``` 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 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.

Comment using (Author doesn't get notification)