How to Pivot Multiple Columns in SQL Server for Optimal Data Presentation?

Pivoting Multiple Columns in SQL Server for Optimal Data Presentation

When working with relational databases, pivoting tables can be an effective way to reshape data for easier analysis and visualization. In SQL Server, this technique can be particularly useful when dealing with tables that have multiple columns you want to display as rows.

Problem Statement:

Consider the following sample table:

Branch  | Category | Sales | Stock  | Target
------- | ******** | ----- | ----- | -------
Branch1 | Panel    | 100   | 200   | 300
Branch1 | AC       | 150   | 250   | 350
Branch1 | Ref      | 200   | 300   | 400

The goal is to pivot the Category column and display the Sales, Stock, and Target columns as rows, yielding the following output:

Branch  | Panel | AC   | Ref  |
------- | ------ | -- | ---- |
Branch1 | 100   | 150 | 200  |

Solution:

To achieve the desired output, we can employ a series of pivot statements in SQL Server. The key is to rename the columns in the intermediate pivot results to prepare for the subsequent pivot operation.

Consider the following code:

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

This code executes the following steps:

1. Prepare the Data: It renames the Category column and adds two additional columns, Category1 and Category2, to facilitate the subsequent pivots.
2. Pivot for Sales: The first pivot statement creates the pv1 table, which sums the sales for each category.
3. Pivot for Stock: The second pivot statement creates the pv2 table, which sums the stock for each category.
4. Pivot for Target: The third pivot statement creates the pv3 table, which sums the target for each category.

The resulting pv3 table contains the desired output, with the categories as rows and the columns representing the sales, stock, and target values.

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