How to Pivot Multiple Columns (Sales, Stock, and Target) in SQL Server for Data Analysis?

Pivoting Multiple Columns for Data Analysis in SQL Server

Data often requires restructuring to facilitate analysis and visualization. Pivoting, a technique that transforms data into a different format, proves invaluable in such scenarios. This article explores the concept of pivoting multiple columns in SQL Server to obtain a new table arrangement.

Problem:

Consider the following scenario:

• A sample table with columns for Branch, Category, Sales, Stock, and Target.
• The goal is to pivot the Category column, placing it as rows, while moving Sales, Stock, and Target to columns.

Solution:

The key to pivoting multiple columns lies in renaming the columns before performing the pivot operation. This ensures that the subsequent pivot statements can successfully aggregate data.

The following modified code achieves the desired transformation:

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

Result:

This refined code executes the pivoting operation, resulting in a table with Category columns as rows and Sales, Stock, and Target as columns.

Additional Note:

For further analysis, you can aggregate pv3 to sum and group data based on specific columns as needed.

The above is the detailed content of How to Pivot Multiple Columns (Sales, Stock, and Target) in SQL Server for Data Analysis?. For more information, please follow other related articles on the PHP Chinese website!