How to Pivot Multiple Columns (Sales, Stock, Target) Simultaneously in SQL Server?

How to Pivot Multiple Columns in SQL Server

Problem:

Suppose you have a table with columns representing item categories (e.g., Panel, AC, Ref) and data such as sales, stock, and target. You want to transform this data into a pivoted format where the categories become rows and the columns hold the respective data for each category (sales, stock, target).

Solution:

To achieve this, you can utilize multiple pivot statements in SQL Server:

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

Explanation:

1. Inner SELECT: Fetch the necessary data from the original table, including the branch name and unsanitized category names.
2. Column Renaming: Rename the category columns with suffixes ('1' and '2') to facilitate subsequent pivoting.
3. First Pivot (pv1): Pivot for the sales column, grouping the data by category.
4. Second Pivot (pv2): Pivot for the stock column using the newly renamed category columns (e.g., Panel1).
5. Third Pivot (pv3): Pivot for the target column using the newly renamed category columns (e.g., Panel2).

Once the pivoting is complete, you can aggregate the results or perform further data manipulations as needed.

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