Thursday, May 26, 2016

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.

4 comments:

  1. Related posts
    http://www.itdeveloperzone.com/2011/01/pivot-with-dynamic-columns.html
    http://www.itdeveloperzone.com/2011/04/columns-to-rows-in-sql.html

    ReplyDelete
  2. Is there a reason why this inline select wouldn't be a good way to get a count of max potential columns for a row?

    Select max(count(productname) over(partition by ordernumber))

    ReplyDelete
  3. Hi Folks,

    I need a help in PIVOT table
    Col A COL B
    ----------------
    1 2
    1 3
    1 4
    2 6
    3 7
    2 8
    4 9
    5 10


    SO ON


    I need a target table as


    Cola A Level 1 Level 2 Level 3 Level 4
    1 2 6 null null
    1 3 7 null null
    1 4 9 null null
    5 10 null null null


    SO ON



    ANy ideas








    1

    ReplyDelete
  4. For SQLServer 11.0.5548.0:

    Need "USE tempdb;" at the top of each block.
    Need "like '#ItemBinMaster%'" instead of "= 'ItemBinMaster'".

    ReplyDelete