How to Pivot Multiple Columns Simultaneously in SQL Server?

Pivoting Multiple Columns in SQL Server

Pivoting is a transformational technique in SQL that allows arranging data from columns into rows. In this context, you aim to pivot the category column into rows and display sales, stock, and target values for each category.

Sample Input:

Branch  Category  Sales  Stock  Target
Branch1 Panel     100    20     30
Branch1 AC        120    30     40
Branch1 Ref       150    40     50

Desired Output:

Branch	Panel	AC	Ref
Branch1	100	120	150
Branch1	20	30	40
Branch1	30	40	50

Solution:

To achieve the desired output, you need to perform multiple pivoting operations:

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

Note:

• The first subquery assigns new names to the Category column with Category1 and Category2 to prepare for multiple pivots.
• Subsequent pivot operations use these extended column names to create rows for each category.
• Aggregations are applied to the columns to sum up values for each category.

Additionally, you can use aggregate functions on pv3 to further summarize the data as needed.

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