Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / sql query to pivot data from rows to columns in sql server
Ask Question
Anubhav Sharma
Anubhav Sharma

Student

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 .

Message

1 Answers

Ravi Vishwakarma
Ravi Vishwakarma 16 Jul 2024
Report Abuse

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