In Sql Server how to Pivot for multiple columns

sql server pivot multiple columns based on one column
dynamic pivot in sql server 2008 on multiple columns
how to use pivot for two columns in sql server
sql pivot multiple rows
pivot in sql w3schools
pivot multiple columns oracle
convert multiple columns to rows in sql server
sql pivot, dynamic columns

This is my sample Table, i want to pivot the category column and get the sales,stock and target as rows

I want the sample output in this form as shown in the below wherein the categories are in place of columns and columns in place of row

Sample Table :

DECLARE @Table1 TABLE 
    (Branch varchar(9), Category varchar(9), Sales INT,Stock INT,Target INT)
;

INSERT INTO @Table1
    (Branch, Category, Sales, Stock,Target)
VALUES
    ( 'mumbai', 'panel', 10,4,15),
    ( 'mumbai', 'AC', 11,7,14),
    ( 'mumbai', 'Ref', 7,2,10),
    ( 'Delhi', 'panel',20,4,17),
    ( 'Delhi', 'AC', 5,2,12),
    ( 'Delhi', 'Ref', 10,12,22)
;

IN SQL SERVER Script :

  Select BRANCH,COL,[panel],[AC],[Ref] from (
    select Branch,Category,COL,VAL from @Table1
    CROSS APPLY (VALUES ('Sales',Sales),
    ('Stock',Stock),
    ('Target',Target))CS (COL,VAL))T
    PIVOT (MAX(VAL) FOR Category IN ([panel],[AC],[Ref]))PVT
ORDER BY Branch DESC

PIVOT on two or more fields in SQL Server – Ken O' Bonn, Here is an example of pivoting on two fields for the SQL community. Having a need to show multiple properties for an entity is a good case for  Converting a Single Row Into Multiple Columns Using PIVOT. A PIVOT operator is used to transpose rows into columns. To convert a single row into multiple columns, perform the following.

You gotta change the name of columns for next Pivot Statement.

Like

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

You are ready to go now....

You can use aggregate of pv3 to sum and group by the column you need.

Pivoting and Unpivoting Multiple Columns in MS SQL Server, Converting Multiple Rows Into Multiple Columns Using PIVOT​​ Multiple rows can be converted into multiple columns by applying both UNPIVOT and PIVOT operators to the result. The PIVOT operator is used on the obtained result to convert this single column into multiple rows. SQL Server - Changing Rows to Columns Using PIVOT 2. Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server In an earlier post I have applied pivoting on one column name ItemColour but here I would like to introduce pivoting on more than one column. So let us jump on example and implement it for multiple columns.

Try below solution

  -- Applying pivoting on multiple columns
SELECT
*
FROM
(
  SELECT 
   Category,
   Sales, 
  FROM TblPivot
 ) AS P

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

union all

 -- For Stock
 SELECT
*
FROM
(
  SELECT 
   Category,
   Stock, 
  FROM TblPivot
 ) AS P

 PIVOT
 (
   SUM(Stock) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv2

union all

 -- For Target
 SELECT
*
FROM
(
  SELECT 
   Category,
   Target, 
  FROM TblPivot
 ) AS P

 PIVOT
 (
   SUM(Target) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv3
 GO

TSQL PIVOT MULTIPLE COLUMNS, Since you want to pivot multiple columns of data, I would first suggest Depending on your version of SQL Server you can use the UNPIVOT function or CROSS  SQL Server Q&A from the SQL Server Central community How to pivot data across multiple columns. The data should be populated in pivot format and the mapping

Following should work,

select * FROM
(
  SELECT 
   Branch,
   Category,
   Sales, 
   Stock, 
   Target
  FROM Table1
 ) AS P
 unpivot
 (
 [Value] FOR [OutPut] IN (sales,stock,[target])
 )unpvt
 pivot
 (
 max([Value]) for  Category in (Panel,AC,Ref) 
 )pvt
order by Branch Desc

SQL Server PIVOT Operator Explained Clearly By Practical Examples, SQL Server PIVOT operator rotates a table-valued expression. It turns the unique values in one column into multiple columns in the output and performs  Sql server pivot multiple columns based on one column (source: on YouTube) Sql server pivot multiple columns based on one column

SQL Pivot Multiple Columns, In my previous article I have given you the idea about the Pivot statement in SQL with multiple real life examples.In this article I would like to  SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output.

Using Pivot for 2 Value Columns, The column 'type1' was specified multiple times for 'p2'. SQL Server 2008 Books Online gives this syntax for “FROM” clause: [ FROM {  FROM - Using PIVOT and UNPIVOT. You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

Questions About Pivoting Data in SQL Server You Were Too Shy to , Yes, you can perform multiple returns pivot column names that are  I would do this slightly different by applying both the UNPIVOT and the PIVOT functions to get the final result. The unpivot takes the values from both the totalcount and totalamount columns and places them into one column with multiple rows. You can then pivot on those results.: See SQL Fiddle with Demo.

Comments
  • @saad you need to respond something for your posted question . Otherwise no one will help you in future
  • The key point here is that you create new category values by appending 1 or 2 to the end. Without doing this, the pivot query won't work properly.
  • I got 'Invalid column name 'Category'. Can you please advice?