Saturday, June 13, 2015

SQL - How can we convert rows into columns

In our daily practice, we need to convert rows values into columns by using SQL. SQL Server introduced Pivot to convert rows into columns which is a very useful feature of SQL. It’s very easy if we need to convert one or two rows into columns due to the defined requirement.
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 '

                print @SQL

                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 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.

Query at a Glance

----- 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;


----- 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;

---- 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

----- 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 '

                print @SQL

                EXEC (@SQL)

------- drop the tables here
                DROP Table #OrderMaster, #OrderDetails;


SQL - How can we convert columns into Rows
In SQL, there are a lot of concepts to store multiple values against a single field to handle the future scope. SQL Server introduced UnPivot to convert columns into row which is a very useful feature of SQL. UNPIVOT require a hard-coded list or dynamic list of columns/values to unpivot them. It requires an assigned alias which is used in the select clause.

No comments:

Post a Comment

Popular Posts