How to Perform a T-SQL PIVOT Operation Without Aggregate Functions?

T-SQL PIVOT operation without aggregate functions

Unlike PIVOT operations, which usually require aggregate functions, PIVOT transformations can also be performed without aggregate functions. This may arise in specific scenarios where data reconstruction is required without aggregation.

Consider the following form:

CustomerID	DBColumnName	Data
1	FirstName	Joe
1	MiddleName	S
1	LastName	Smith
1	Date	12/12/2009
2	FirstName	Sam
2	MiddleName	S
2	LastName	Freddrick
2	Date	1/12/2009
3	FirstName	Jaime
3	MiddleName	S
3	LastName	Carol
3	Date	12/1/2009

The goal is to pivot this data to the following results:

CustomerID	FirstName	MiddleName	LastName	Date
1	Joe	S	Smith	12/12/2009
2	Sam	S	Freddrick	1/12/2009
3	Jaime	S	Carol	12/1/2009

To achieve this using PIVOT without an aggregate function, you can use the following query:

SELECT CustomerID,
       MIN(CASE DBColumnName WHEN 'FirstName' THEN Data END) AS FirstName,
       MIN(CASE DBColumnName WHEN 'MiddleName' THEN Data END) AS MiddleName,
       MIN(CASE DBColumnName WHEN 'LastName' THEN Data END) AS LastName,
       MIN(CASE DBColumnName WHEN 'Date' THEN Data END) AS Date
FROM table
GROUP BY CustomerId;

This query executes a case statement in a PIVOT operation to select the minimum value of each DBColumnName for a given CustomerID. Since there is only one row of data for each CustomerID and DBColumnName combination, the MIN function returns the required value.

The above is the detailed content of How to Perform a T-SQL PIVOT Operation Without Aggregate Functions?. For more information, please follow other related articles on the PHP Chinese website!