SQL Server – -Simple way to transpose rows into columns

In this post, we’ll discuss how to transform rows into columns in the SQL server. This is yet another common interview question. To get the best 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 got  two rows, one for each of the employee’s within that department, and the same is the case for Research and Development and Accounting and Finance.

And if you look at the results 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 look 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 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 some columns from TblDeparmentEmployee table. So the columns that I want to select are Department, EmployeeName, and row number and a bit we’ll discuss, the purpose of generating this row number and 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 select row_number over a dataset that is partitioned by department column, and that is ordered by Department column, and let’s give  row_number a column name, and let’s call this ColumnSequence. And let’s execute the select 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 number there and the reason for generating  row number and look at below image, the row numbers are unique only for a given Department.

and what we want in the output. We want the column names to be EmployeeName1, EmployeeName2, two rows to generate those column names.

What I am going to do is concatenate the word EmployeeName to these numbers and to achieve that, the easiest way is to simply, append this 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  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.

So now let’s go and 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

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

The post SQL Server – -Simple way to transpose rows into columns appeared first on Software Development | Programming Tutorials.