How to Pivot Multiple Columns in SQL Server Using Multiple PIVOT Statements?

Pivoting Multiple Columns in SQL Server

In a database table where data is arranged in rows and columns, sometimes it becomes necessary to transform the table structure by switching rows with columns. This technique is known as pivoting.

Scenario:

You have a sample table with columns such as Branch, Category, Sales, Stock, and Target. You want to pivot the table using the Category column and arrange the remaining columns (Sales, Stock, Target) as rows. The desired output should have categories as columns and values as rows.

Solution:

To pivot the table, you can use multiple PIVOT statements with suitable column name modifications:

SELECT
*
FROM
(
  SELECT 
   Branch,
   Category,
   Category+'1' As Category1,
   Category+'2' As Category2,
   Sales, 
   Stock, 
   Target
  FROM TblPivot
 ) AS P

 -- For Sales
 PIVOT
 (
   SUM(Sales) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv1

 -- For Stock
 PIVOT
 (
   SUM(Stock) FOR Category1 IN ([Panel1], [AC1], [Ref1])
 ) AS pv2

 -- For Target
 PIVOT
 (
   SUM(Target) FOR Category2 IN ([Panel2], [AC2], [Ref2])
 ) AS pv3
 GO

By modifying the column names (e.g., Category '1' and Category '2'), you can ensure that the PIVOT statements work correctly.

The intermediate tables (pv1, pv2, pv3) will provide the pivoted data for Sales, Stock, and Target respectively. You can then aggregate the results of pv3 to group and sum the values as needed.

The above is the detailed content of How to Pivot Multiple Columns in SQL Server Using Multiple PIVOT Statements?. For more information, please follow other related articles on the PHP Chinese website!