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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- How do UPDATEs that use JOINs behave with multiple records?
- Return all possible values from xQuery
- How can I edit values of an INSERT in a trigger on SQL Server?
- is there a tool to see the difference between two database tables in SQL Server?
- SQL wildcard contains the word and not as part of another word
- How do I elegantly import an Excel file into Sql Server via a Coldfusion HTML form?
- Type mismatch in SQL
- C# CLR throws security exception unless marked as UNSAFE
- How to add a default clause in CASE
- SQL Nested IF-ELSE Statements
- How to compare two given dates with date coming from datetime field in sql server
- Moving Database from one availability Group to another sitting on different SQL Instance
- Swedish characters from javascript to mssql - appear as question marks in db table?
- Indexed views: How to insert value into other table with index views?
- TSQL - Columns to Rows with New Columns
- C# DataSource Class and Thread Safety
- SQL Join & Count
- Does SqlCommand.ExecuteNonQuery waits till completion of stored procedure?
- Two Conditions Where MDX Query
- Connecting to an Azure database using SQLAlchemy in Python
- Should I ever need to call RTRIM() on a varchar or nvarchar value?
- How do I prevent SQL Server sysadmin users write access?
- How to do Order By with null at the end
- Using a View from SQL Server 2008 in C# & Asp.net
- SQL Server 2008 R2 Job Launched Step 1 hundreds of times
- Reading a string expression with parameters and executing the expression as date
- Deleting rows from table
- Passing Binary Data to a Stored Procedure in SQL Server 2008
- .NET SqlDataReader: SqlException was unhandled by user code
- Convert two columns into 5 rows in sql server
- How do I specify to use server defined values in a Table Valued Parameter when my source is a DataReader?
- CASE Statement Workaround to Set Values within a Group of IDs
- Transaction Concurrency Isolation - Why can I update a subset of another transactions records?
- Creating dynamic SQL for a lot of columns
- SQL Trigger to Call a Stored Procedure with inserted value as parameter
- Windows Azure, Entity Framework. Keyword not supported: 'metadata'.
- SQL Server 2005 Isolation level changes on service restart, how to stop it?
- SQL row output like column
- How to perform bitwise OR operation between two rows in same SQL table?
- Can you only parameterize the where clause?
- Need help in bit complicated SQL query
- Connections in loop for SQL Server Symfony2
- MS SQL index rebuild fixes timeouts
- Installing TFS Express 2017 fails with SQL Server Express 2017
- Performance of separate SQL commands versus a single stored procedure
- Can't connect to Azure DB from Azure website
- SQL Server 2008 - "Conversion failed when converting from a character string to uniqueidentifier." error
- SQL Server: halt an INSERT in a trigger
- HAVING MAX in SQL Server 2005
- T-SQL Calculate duration in months between different years of ranges
- NULL check in WHEN statement is not working in SQL Server
- Unable to write data to the transport. Vs2017 ASP.net core Scaffolding ( MSSQL WINDOW 10)
- Selecting All the columns except IDENTITY Column
- SQL How to order each entry by date
- When creating multiple datagridview controls at runtime, how do you hide columns?
- Exclude apostrophes from SQL Server Full Text Search
- Error Inserting From Inner Join and EXCEPT clause SQL. conversion failed when converting the varchar value 'Error:' to data type int
- Can't export SQL Azure database when stored procedure is encrypted
- How do I fill a temp table iteratively and stuff() the result into columns?
- What port is a remote SQL Server 2005 running on?
- SQL Dacpac Deploy using SqlPackage and DropObjectsNotInSource with DoNotDropUsers
- SQL Server varbinary(max) to Oracle LONG RAW
- Can a SELECT statement return data and INSERT in to another SQL table at the same time?
- Database Denormalization for Performance Gains for SELECT reports
- Linq updates extra rows
- How to get detailed list of connections to database in sql server 2005?
- Create new SQLCommand's or reuse the same one
- I'm trying to do multiple left joins in SQL Server and I'm getting unexpected results when a value is not found in the table being joined
- Repeat SQL query and create one output column
- Get Unique Results in a query
- How do I get values for all months in T-SQL
- Why can't one join with a table returned from a table valued function?
- Column name or number of supplied values does not match table definition without dropping table
- T-SQL: create column sequence relative to another column
- SQL- row values for same id as list (,)
- Is it possible that SqlException is thrown and property Number == 0?
- DELETE specific rows that matches all values
- Trigger not calling for huge rows insert
- PIVOT with multiple columns
- SQL INSERT Statement By Comparing and Retrieving Data Involving Multiple Columns
- QtSql connect to multiple databases
- Importing data from Excel into a Linked SQL Server Table in MS Access
- SqlDateTime.MinValue != DateTime.MinValue, why?
- Substring and Charindex
- Show Time Difference Out of Order
- SP or Function on UnPivoted data using column names
- Multiple storage of images and BLOB files in SQL Server
- Is there a benefit to storing plain-text (in addition to the HTML data) to improve searchability?
- Generate a hash for a set of rows in sql server
- Using SQL SUM with Case statement containing inner SELECT
- Is it possible to insert current datetime in a stored procedure which is designed to insert data?
- How to parameterize query with like operator in Sql Server
- Add a column ID with a unique value for each row
- SQL condition sum from two joined tables
- SQL Server 2008 R2 RTM on MSDN?
- SQL Server and Oracle read / write roles
- Average of last n or 10 rows only in SQL Server
- creating a mailing lists to sending mass email in asp.net
- Query with filter using variable is not using index
- Copying multiple columns from one SQL table to another
- Iterative Query for Spatial Records
- Prevent EF data migrations from dropping column
- simple select is taking huge time on the table
- SQL Server : character showing question mark
- SQL Server 2008: Collation for UTF-8 code page 65001
- SQL Table datetime column format
- How to process dynamic partitions in SSAS
- Access multiple instances in SQL Server
- Check if all word fragments exist in a text
- Unable to group after left join
- How to call a user defined function with NULL values for one of its input parameter?
- SQL update statement from select
- Can GetSchema method work asynchronously?
- TSQL to identify long Float values
- CTE with additional date lookup returning NULL
- SQL executing twice in IIS10 with ASP Classic
- How to create SqlDataConnection for db with table 'System'?
- set default start day as monday in sql server
- Google Cloud SQL (SQL Server) restore external backup files into current database
- SQL Server tsql parenthesis logic?
- SQL convert columns to rows
- Create a table variable instance inline
- Error converting data type nvarchar to numeric
- SQL - Grouping segments for AVGs and COUNTs
- The underlying provider failed on ConnectionString (|DataDirectory| and LocalDB)
- How to use DATEDIFF to return year, month and day?
- how did Id get generated in sql server 2008 r2?
- How to create unique serial number strings in a particular format?
- Select last updated row ID and last deleted row ID in trigger before they occur in SQL Server
- SQL server - Limit search to a maximum value in a column
- Crystal Reports assumes wrong datatype of column from stored proc
- Unit Testing a Data Access Layer
- LINQ Union vs SQL Union
- Using .Net 3.5 assemblies SQL 2005 CLR?
- SQL Server and Microsoft.ACE.OLEDB.12.0
- How do I supply the FROM clause of a SELECT statement from a UDF parameter
- insert result of dynamic pivot query into temp table
- What is the alternative for number(2,0) of oracle in sqlserver?
- Dataset or DataGrid to SQL Server Database Table
- How to enter nvarchar data type in varcher datatype column in SQL Server?
- Update an Access Link Table when the underlying SQL Server table's columns change
- Can we use 'GO' multiple times in SQL Transaction?
- Convert SQL Server T-SQL wide result to readable text format
- what is the default concurrency control that sql server 2005 uses?
- How format datetime for sql server in laravel?
- Notify user history scheme design
- How can I debug and find the reason for stuck SSIS packages?
- How to provide YTD, 12M, and Annualized measures in SQL Server data warehouse?
- SQL: Select Distinct on 1 column
- Unable to Auto-Create ASPNETDB.MDF - Visual Web Developer Express 2008 / SQL Express 2008