forum

Home / DeveloperSection / Forums / SQL Query to Pivot Data from Rows to Columns in SQL Server

Ask Question
Anubhav Kumar

Anubhav Kumar

Follow
The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Can you answer this question?

Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

16-Jul-2024

A PIVOT table in SQL Server is a way to convert data from rows to columns, grouping values ​​as needed. It is particularly useful for collecting and analyzing data in a readable format.

Syntax-

SELECT *
FROM (
   -- Subquery that retrieves the data to pivot
   SELECT <non-pivoted column(s)>,
          <pivot column>,
          <value column>
   FROM <source_table>
) AS SourceTable
PIVOT (
   -- PIVOT function parameters
   <aggregate_function>(<value column>)
   FOR <pivot column> IN (<column1>, <column2>, ... <columnN>)
) AS PivotTable;

Example- 

Let's see we have a sample SQL table “EmployeeDetails” that contains information about Employees,

use MyCollegeDb

select * from 
(
	Select EmpName, Gender, Location from EmployeeDetails
) AS EMP
PIVOT(
	Count(EMP.EmpName) FOR Location IN([China],[India],[Rasia])
)AS [PvtTable]

Or Match in the whole location

USE MyCollegeDb;

-- First, get a list of distinct locations to include in the PIVOT clause
DECLARE @locations NVARCHAR(MAX);
SELECT @locations = STRING_AGG(QUOTENAME(Location), ',') 
FROM (SELECT DISTINCT Location FROM EmployeeDetails) AS Locs;

-- Construct and execute the PIVOT query dynamically
DECLARE @sql NVARCHAR(MAX) = '
SELECT *
FROM 
(
    Select EmpName, Gender, Location from EmployeeDetails
) AS EMP
PIVOT (
    COUNT(EmpName) 
    FOR Location IN (' + @locations + ')
) AS PvtTable
';
--print @sql; -- See dynamic query
EXEC sp_executesql @sql;

Explanation

  • First of all, get all distinct locations, and combine the whole locations in one string format
  • Second, write the PIVOT query dynamically to evaluate it.

 

Read more 

Define the PIVOT Table with examples in the SQL server.

SQL Query to Get Yearly Aggregated Data in SQL Server

Help with Writing a Query to Combine Multiple Rows into One in SQL Server

How to Write a Query to Find the Longest String in a Column in SQL Server?

SQL Query to Get Distinct Values from a Column in SQL Server

 

MindStick Training

Liked By