Simple way to Transpose Rows into columns using SQL Server

In this post, we'll discuss how to transform rows into columns in SQL Server. This is yet another common interview question. To get the most out of this post, ...

I strongly recommend read Pivot Operator Concept ,in this post, we discuss the basics of the Pivot Operator. So if you are new to pivot operator, I strongly recommend reading that post first.

Now here is the interview question.“Write a SQL query to transpose these rows into columns.” Let’s understand the concept using the below table ,TblDeparmentEmployee.

If you notice, Marketing has two rows, one for each of the employees within that department, and the same is the case for Research and Development and Accounting and Finance.

And if you look at the result set on the right-hand side, these employee rows are actually converted into columns, as you can see in the above image.

How to convert rows into columns in sql query

So let’s see how to transpose these rows into columns. Obviously, the first step here is to create the table itself, and here is the SQL script to create the table and the SQL script populated with some test data.
Sql Script

CREATE TABLE [dbo].[TblDeparmentEmployee](
    [Department] [nvarchar](max) NULL,
    [EmployeeName] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Marketing ', 'Adam')
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Marketing ', 'Austin')
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Research and Development', 'Evan')
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Research and Development', 'Jameson')
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Accounting and Finance', 'Jason')
GO
INSERT [dbo].[TblDeparmentEmployee] ([Department], [EmployeeName]) VALUES ('Accounting and Finance', 'Declan')

So we want to select certain columns from the TblDepartmentEmployee table. The columns that I want to select are Department, EmployeeName, and a row number. Later, we'll discuss the purpose of generating this row number. To generate the row number, I’m going to use the row_number function.

Select Department, EmployeeName1, EmployeeName2
From
(
Select Department, EmployeeName,
'EmployeeName'+
cast(row_number() over(partition by Department order by Department)
as varchar(30)) ColumnSequence
from TblDeparmentEmployee
) Temp
pivot
(
max(EmployeeName)
for ColumnSequence in (EmployeeName1, EmployeeName2)
) Piv

Query explanation

So we want to apply the row_number function over a dataset that is partitioned by the department column and ordered by the Department column. Let’s give the row_number function a column name, and let’s call this column ColumnSequence. Now, let’s execute the select query and see the output that we get.

Select Department, EmployeeName,
cast(row_number() over(partition by Department order by Department)
as varchar(30)) ColumnSequence
from TblDeparmentEmployee

So we get the row numbers there, and the reason for generating row numbers is evident. If you look at the image below, the row numbers are unique only for a given Department.

Now, what we want in the output is to have column names like EmployeeName1, EmployeeName2, and so on. To generate those column names, I am going to concatenate the word 'EmployeeName' with these numbers. The easiest way to achieve that is to simply append the text 'EmployeeName' to whatever number this row_number function is returning.

Select Department, EmployeeName,
'EmployeeName'+
cast(row_number() over(partition by Department order by Department)
as varchar(30)) ColumnSequence
from TblDeparmentEmployee

Now, this is the select query, which is going to return us a ResultSet on which we are going to apply the pivot operator. At the moment, notice the EmployeeName1, EmployeeName2, these are still rows within the ResultSet.

Now, we’ll see how to use the pivot operator, which is going to transpose these rows into columns.

Let’s apply the pivot operator. The first thing that we need to specify here is the aggregate function. So I’m going to use the max aggregate function.

Now, when we use Max or Min, it doesn’t really make any difference, at least for this query. But to satisfy the query, I’m going to use this max aggregate function, and the column is going to be EmployeeName because that’s what we want to pivot.

pivot
(
max(EmployeeName)
for ColumnSequence in (EmployeeName1, EmployeeName2)
) Piv

Next, we have to specify the name of the column which contains the values that we want to transpose to become columns. So here, the column sequence is containing EmployeeName1, EmployeeName2, so that's the column which contains the values that should effectively become the pivoted columns.