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.
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
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 to Split SQL column and get a part at condition
- Refreshing a table in SQL server management studio
- Return all rows from a table and indicate with a new column whether they exist or not in another table
- Declare a Table Variable Based on Select Statement
- How to group similar rows in SQL Server
- SSIS Script Component or Script Task Write To Job History Log
- Execute multiple statements inside Stored Procedure
- Tips for improving performance of DB that is above size 40 GB (Sql Server 2005) and growing monthly by around 3GB
- Pass list to SQL custom type
- TOP slows down query
- Ignore duplicate records in case when statement
- Conversion failed when dealing with ambiguous varchar (uniqueidentifier or not) in TSQL
- Getting first and last values from contiguous ranges
- Sql Server subscription agent in running state
- Getting the above average student from database
- SQL multiple INSERT OR UPDATE
- Disabling SSIS Package Validation when Loading a Package in the VS Designer
- SQL Server 2012:Generate column values Dynamically based on concatenation of other column values
- SQL Server constraint not deleting
- How to insert a c# datetime var into SQL Server
- Get hierarchical categories of a parent category
- Extract Number from VARCHAR
- Get Sql Server Table using Table Name
- Retrieve data from SQL Server with AND/OR when some parameters may be left blank
- Hierarchical Data Structure Design (Nested Sets)
- Insert into a table a part of another table
- Generating xml with SQL Server with child namespace
- Reading JSON file into a table
- Query for effective date based on related table values
- DateDiff() Hours and Minutes?
- Has EF6+ / 7 added any ways that I can add update child tables?
- Saving an image to SQL Server 2008?
- Restore SQL Server database from snapshot in C#
- SqlException (0x80131904): Login failed for user 'db123'
- How to transpose a group by SQL table
- SSIS Azure BLOB / DW Upload task error
- Merging two tables
- I need help with a sql query
- SQL query for conditional select
- Get the last date in a one to many relationship in sql
- Use Column value as string pattern in REPLACE function
- SSRS 2008 R2 Carriage Return Problem
- SQL Server GUID (from Active Directory) vs Int
- Queries for Sql Server and Oracle
- Bulk Insert any Compressed format in SQL Server Table
- Transforming and repeating multiple rows
- Count rows within each group when condition is satisfied Sql Server
- Date of inserting a row into table
- IF Else in function
- Redis as a cache for RDBMS
- Update table to inserted value where current value is in deleted table
- Get backup from a remote SQL Server
- SQL Show Number of Months and Years Between Two Dates
- System.Data.SqlClient.SqlException after CREATE/ALTER/PRINT
- Conversion failed when converting date and/or time from character string
- SQL Query for Json object inside Json
- Named parameter query not behaving as hoped:
- SQL Server query dry run
- Why am I getting an error message that says "Invalid Object Name" when I've declared my variable in the current batch?
- The column 'TRANSACTION_EVENT_ID' was specified multiple times for 'YY'
- How to display loading graphics in Firefox in Microsoft Reporting Services
- Problems logging into SQL Server 2012 (64 Bit)
- MERGE DELETE only based on parent id
- Converted "Linq query to Sql query" doesn't make sense to me
- Switch from SQL Server Express to SQL Server Developer edition on the same computer
- How to Stopping System-Versioning on a System-Versioned Temporal Table in SQL Server 2016?
- SQL Function "declare scalar variable"
- Connecting to SQL Server with HeidiSQL
- Error :RESTORE cannot process database 'Test_DB' because it is in use by this session
- How would I use an audit trail to display which fields have ever been edited?
- How can I group by day, and still return a datetime?
- How to get top 'n' percentage values
- Error trying to restore database in SQL Server 2012
- While linked server is closed, exec the stored procedure will get an error
- Assigning @@rowcount to variable in SQL Server stored procedure
- How to set timeout for long running queries in gorm
- How Grant Select access to view in schema
- SQL Server - Setting Multiple Values in a Case Statement?
- Microsoft Sync multiple scope with SQL Server 2005
- C# update SQL Server table
- How to reset auto-increment in SQL Server?
- Unique string for each record in the database table
- Show last 5 records in sql without using desc
- SQL Server CONVERT int to varchar with padding
- How to do Migrations and SQL Server connection on MacOS
- How do I "run the SQL Server Management Object DLL files through a linker"?
- Query two databases with Oracle SQL Developer
- Conditions if not null in where in SQL Server
- Passing an SSIS variable value as a parameter of SP via OLE DB Command
- Is it possible to insert a column as a parameter in a user-defined function?
- Filter record based on a column using SQL Server
- Unable to comprehend why a WHERE clause is being accepted
- Compute data between First and Last Day Of Last Month
- Query with CROSS APPLY, trying to get a field based on an attribute value
- SQL SUM logical Error
- Remove all shorter subsequences containing an ID in a recursive CTE
- SSRS dynamic parameters passed through page header
- How do I "Install" a SQL Server Database to a Remote Server Without Physical Access?
- SSDT Data comparison, login failed
- Select distinct records from MS SQL database when querying row numbers
- Select Count(*) vs Select Count(id) vs select count(1). Are these indeed equivalent?
- unzipping a file using 7zip in SSIS
- IN statement in result expression in CASE THEN expression
- SQL Server stored procedure to add days to a date
- How to select records from a table as a pair
- How to dump the script from sql server?
- how to get the 30 days before date from Todays Date
- MS SQL server data truncation on table creation
- SQL Query conversion from Access to MySQL
- How to compare varchar parameter to null in a natively compiled stored procedure?
- string or binary data would be truncated error message
- How to to make a bulk-insert from SQL Tables into Postgres using Powershell
- Read xml through sql query
- Connect to MSSQL using php from Mac OS X, "No data received"
- SQL Script run on this server but don't on other server
- SQL Server - Forcing a Row To display In A ResultSet Where No Values Exist
- SQL Server group by date
- Delete rows using a GUID key in SQL Server
- Is it possible to pass a table valued parameter to a stored procedure and return a table result?
- Cumulative Summation in SQL Server
- Save byte array in sql server
- SQL: Select the same column twice in one query?
- How to pass multiple parameters for executable path in the Process Task (SSIS)?
- Count and Row_Number
- SQL Statement from DML Trigger
- Moving webshop storage to NoSQL solution
- Struggling to interpret this SQL join syntax
- Gap in IDENTITY column - gigantic - MS SQL Server 2008 R2 Standard 64 bit SP3
- SL app doesn't connect to Database from IIS, but does from VS2010 - SQL Server Auth, not Wndws Auth
- How to get only top 1 row from second table in SQL Join
- From Month Name and year Get date range of that month in sql
- Is there any open source software for converting SQL statements to LINQ?
- Inner Join with two tables, and Left Join the result with another table
- How to replace substring in a column using lookup table in sql server
- Force Entity Framework 6 (EF6) to use nvarchar(MAX)
- Follow the MS tutorial, but see the error [Microsoft][ODBC Driver Manager] Connection not open ODBC Error in SQLDisconnect
- SQL Server : backup database with T-SQL?
- How do I find out if a column is required or nullable using a SQL statement?
- What is the counterpart for SUM() in SQL Server?
- Database Design: Index Varchar
- Add historical data for key field in separate column (previous contract)
- CREATE ASSEMBLY fails with "Unable to resolve token"
- Changing date format via VBA to SQL query
- SQL Task help pls
- Latest Available Holdings by Quarter
- How to handle parallel bulk update requests in SQL Server?
- Insert statement conflicts with FK constraint on ApplicationUsers
- How do I specify a column to be unique in the scope of a relationship in SQL Server 2008?
- How to generate random nvarchars in sql server
- Get a list of dates between two dates using a function