SQL – PIVOT and UNPIVOT operators

These beautiful operators were introduced with SQL Server 2005 released. They are working vice versa with each other. PIVOT is used if we need to rotate rows into columns and performs aggregations where they are required on any remaining column values that are wanted in the final output where as UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. We can use dynamic T-SQL script to create dynamic PIVOT for having dynamic columns as well as for create dynamic UNPIVOT for having dynamic rows.

We will demonstrate both of them with proper examples here-

PIVOT - How can we convert rows to columns in SQL?

Sometimes, it’s going very hectic to us because we don’t know the actual rows which need to be converted into columns.

Case 1: If we need to display the addresses against the customer then customer can have two or three addresses such as billing address, official address or permanent address.

In the above case, we don’t need to be worried due to the limitation of the possibilities of the requirement.

Case 2: If we need to display the products against any order then an order may have one to more products could be sold out.

In the above case 2, numbers of columns for products are not fixed and we cannot handle them by using the static values. We need here to use a SQL script to generate the dynamic columns against the number of rows of any order.

So, we are here to work on the case 2. We are creating two temporary tables such as –

              1) Temp Order Master to hold the Order Id and Customer Name.

----- Create OrderMaster Table in the database CREATE TABLE #OrderMaster  (OrderId Varchar(10), CustomerName Varchar(25)) ----- Insert values in the OrderMaster table Insert into #OrderMaster values ('2015060001', 'Ryan Arjun'), ('2015060011', 'White Gray'),  ('2015060521', 'Lucy White') ---- select values from the table select * from #OrderMaster; OrderId CustomerName 2015060001 Ryan Arjun 2015060011 White Gray 2015060521 Lucy White

    

          2) Temp Order Details to hold the products against a particular order.

----- create #Order Details table to hold the items to the respective order CREATE TABLE #OrderDetails  (OrderId Varchar(10),ProductName Varchar(25)) ------ Insert Values respective with order master Insert into #OrderDetails  values ('2015060001', 'SONY LED'),('2015060001', 'Tata Sky'),('2015060001', 'iPhone'),('2015060011', 'Juice Maker'),('2015060011', 'Switch Board'), ('2015060521', 'Voltas AC') ------ Select values from the order details select * from #OrderDetails; OrderId ProductName 2015060001 SONY LED 2015060001 Tata Sky 2015060001 iPhone 2015060011 Juice Maker 2015060011 Switch Board 2015060521 Voltas AC

In the above temporary tables, Order Id is the column table.

Order Id (2015060001) has the three rows to hold the products against this order and we need to generate three columns.

Order Id (2015060011) has the two rows to hold the products against this order and we need to generate two columns.

Order Id (2015060521) has the one row to hold the products against this order and we need to generate one column.

But ideally, we need to generate the three columns for products to meet the requirement. If any order has one single product then other product columns will be blank for this type of order.

The Most Possible Columns

Here we need to get the maximum possible columns. We will use the local variables to get the most possible number of columns which will be based on the number of rows are available in the table such as –

---- declare variable to get the most possible number of item against any order declare @MaxColumn  int ----- set the values select top 1 @MaxColumn =count(ProductName) from #OrderDetails  group by OrderId order by count(ProductName) desc ----- declare local variable to hold the name of the columns for the product here declare @COLS  nvarchar(max) ='', @CTR int = 1 ---- here set the name of the columns here which will use in pivot table while (@CTR<=@MaxColumn) begin ------ For the first column IF @CTR=1 set @COLS = @COLS +'[ProductName'+convert(nvarchar(10),@CTR)+']' ------ for the other columns ELSE set @COLS = @COLS +', [ProductName'+convert(nvarchar(10),@CTR)+']' --- set the increment of the counter set @CTR=@CTR+1 end ----- Possible columns select @COLS as Columns Columns [ProductName1], [ProductName2], [ProductName3]

Now, we are very clear about the most possible rows which will be needed to convert into columns as shown above.

Pivot with Dynamic Query

Pivot is the SQL feature to convert rows into columns. We need to generate a dynamic query to with Pivot and pass the most possible columns as shown below-

----- declare local variable to get the dynamic script Declare @SQL NVarchar(max)  set  @SQL = ' SELECT PVT.*                 FROM     (                 SELECT  OM.OrderId, OM.CustomerName,                 ProductName= ''ProductName''+ CONVERT(VARCHAR(12),(ROW_NUMBER()     OVER(PARTITION BY OM.OrderId ORDER BY OD.ProductName)))                               FROM               #OrderMaster OM                inner join #OrderDetails OD                ON OM.OrderId = OD.OrderId                 ) AS OMD                 PIVOT                 (                 Max(ProductName) FOR ProductName IN ('+@COLS+')                 ) AS PVT '                  EXEC (@SQL) OrderId CustomerName ProductName1 ProductName2 ProductName3 2015060521 Lucy White ProductName1 NULL NULL 2015060001 Ryan Arjun ProductName1 ProductName2 ProductName3 2015060011 White Gray ProductName1 ProductName2 NULL print @SQL ----- Print Output – How does script will work SELECT PVT.*      FROM     (                 SELECT     OM.OrderId, OM.CustomerName, ProductName= 'ProductName'+ CONVERT(VARCHAR(12),(ROW_NUMBER() OVER(PARTITION BY OM.OrderId ORDER BY OD.ProductName)))                 FROM #OrderMaster OM inner join #OrderDetails OD ON OM.OrderId = OD.OrderId                 ) AS OMD                 PIVOT                 (                 Max(ProductName) FOR ProductName IN ([ProductName1], [ProductName2], [ProductName3])                 ) AS PVT ------- drop the tables here                 DROP Table #OrderMaster, #OrderDetails;

We are able to see here that dynamic script is the way to get the pivot columns from the most possible columns.

UNPIVOT - How can we convert columns to Rows in SQL?

To display the columns to rows is become urgent. In my scenario, we have a table called Item Bin Master.  

In this table, we have multiple User Defined Field such as shown below:

Item_No	BIN_No	user_def1	user_def2	user_def3	user_def4
MOT XU1100	FAMOUS	MOT U1100_1	MOT XU1100_2	MOT XU1100_3	MOT XU1100_4
MOT XV1100	FAMOUS	MOT XV1100_1	MOT XV1100_2	MOT XV1100_3	MOT XV1100_4

On the basis of the above data, we need to change the output of the data as shown below:

Item_No	BIN_No	User  Defined Field	User Defined Field Value
MOT XU1100	FAMOUS	user_def1	MOT XU1100_1
MOT XU1100	FAMOUS	user_def2	MOT XU1100_2
MOT XU1100	FAMOUS	user_def3	MOT XU1100_3
MOT XU1100	FAMOUS	user_def4	MOT XU1100_4
MOT XV1100	FAMOUS	user_def1	MOT XV1100_1
MOT XV1100	FAMOUS	user_def2	MOT XV1100_2
MOT XV1100	FAMOUS	user_def3	MOT XV1100_3
MOT XV1100	FAMOUS	user_def4	MOT XV1100_4

We still know the important of the PIVOT in the SQL. Here, we will use the UNPIVOT feature of the SQL to columns to rows.

In the above case, numbers of rows for items are not fixed and we cannot handle them by using the static values. We need here to use a SQL script to generate the dynamic rows against the number of columns of any item.

We are creating a temporary tables such as –

1)      Temp Order Master to hold the Order Id and Customer Name.

--- create temporary table CREATE TABLE #ItemBinMaster (                 [item_no] [char](30) NULL,                 [bin_no] [char](8) NULL,                 [user_def1] [varchar](32) NULL,                 [user_def2] [varchar](32) NULL,                 [user_def3] [varchar](32) NULL,                 [user_def4] [varchar](32) NULL ) ----- Insert values into the temp table INSERT INTO #ItemBinMaster Values ('MOT XU1100', 'FAMOUS', 'MOT XU1100_1', 'MOT XU1100_2', 'MOT XU1100_3', 'MOT XU1100_4'), ('MOT XV1100', 'FAMOUS', 'MOT XV1100_1', 'MOT XV1100_2', 'MOT XV1100_3', 'MOT XV1100_4') ---- select values from the temp table select * from #ItemBinMaster; Item_No BIN_No user_def1 user_def2 user_def3 user_def4 MOT XU1100 FAMOUS  MOT U1100_1 MOT U1100_2 MOT U1100_3 MOT U1100_4 MOT V1100  FAMOUS  MOT V1100_1 MOT V1100_2 MOT V1100_3 MOT V1100_4

But ideally, we need to generate the four rows for the each item to meet the requirement.

The Most Possible Rows

Here we need to get the maximum possible Rows. We will use the local variables to get the most possible number of rows which will be based on the number of columns are available in the table against such as –

----- check the column name in the table select  Column_name from information_schema.columns            where table_name = 'ItemBinMaster' and Column_name like 'user_def%' Column_name user_def1 user_def2 user_def3 user_def4 ----- declare local variable DECLARE @Unpivot AS NVARCHAR(MAX)='',    @SQL  AS NVARCHAR(MAX) ----- get the values for unpivot here select @Unpivot   = stuff((select ','+quotename(C.column_name)            from information_schema.columns as C            where C.table_name = 'ItemBinMaster' and                  C.column_name like 'user_def%'            for xml path('')), 1, 1, '') ---- Unpivot column names select @Unpivot as Unpivots Unpivots [user_def1],[user_def2],[user_def3],[user_def4]

Now, we are very clear about the most possible columns which will be needed to convert into rows as shown above. 
The brackets aren't mandatory. They are only needed when the identifier used requires quoting in order to prevent errors, as is the case with column names that are numbers. If the values/column names start with a letter and don't contain invalid characters, no square brackets are necessary. 

UnPivot with Dynamic Query

UnPivot is the SQL feature to convert rows into columns. We need to generate a dynamic query to with UnPivot and pass the most possible columns as shown below-

---- local variable Declare  @SQL  AS NVARCHAR(MAX)='' ----- set the values in the dynamic query set @SQL   = 'select Item_No, Bin_no,         [User  Defined Field],         [User Defined Field Value]      from #ItemBinMaster      unpivot      (         [User Defined Field Value]         for [User  Defined Field] in ('+ @Unpivot +')      ) up' ---- print sql value in the dynamic query print @SQL select Item_No, Bin_no,         [User  Defined Field],         [User Defined Field Value]      from #ItemBinMaster      unpivot      (         [User Defined Field Value]         for [User  Defined Field] in ([user_def1],[user_def2],[user_def3],[user_def4])      ) up ------ get the values here exec sp_executesql @SQL; Item_No BIN_No User  Defined Field User Defined Field Value MOT XU1100 FAMOUS user_def1 MOT XU1100_1 MOT XU1100 FAMOUS user_def2 MOT XU1100_2 MOT XU1100 FAMOUS user_def3 MOT XU1100_3 MOT XU1100 FAMOUS user_def4 MOT XU1100_4 MOT XV1100 FAMOUS user_def1 MOT XV1100_1 MOT XV1100 FAMOUS user_def2 MOT XV1100_2 MOT XV1100             FAMOUS  user_def3 MOT XV1100_3 MOT XV1100         FAMOUS  user_def4 MOT XV1100_4

We are able to see here that dynamic script is the way to get the pivot columns from the most possible columns.

Conclusion
We can group data by more than one column when we use the PIVOT/UNPIVOT operator. There is a way to work around SQL Server’s restriction against using COUNT(*) in the PIVOT clause and easily possible to pivot more than one column when pivoting data in SQL Server. These operators allow us to perform multiple aggregations one after another one. By the help of dynamic T-SQL script, we can pivot/unpivot data if we don’t know the values in the pivoted column/unpivoted rows.