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;
|
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;
|
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
|
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)
----- Print Output – How does script will work
------- 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.
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