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.
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;
|
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%'
----- 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
|
Now, we are
very clear about the most possible columns which will be needed to convert into
rows as shown above.
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
------ get the values here
exec sp_executesql @SQL;
|
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 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;
----- check the column name in the table
select Column_name
from information_schema.columns
where table_name = 'ItemBinMaster' and Column_name like 'user_def%'
----- 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
----- 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
------ get the values here
exec sp_executesql @SQL;
----- drop temp atble
drop table
#ItemBinMaster
|
In our daily practice, we need to convert rows values into columns by using SQL. Sometimes it’s very easy to us if we need to display one or two rows into the columns due to the defined requirement. But sometimes, it’s going very hectic to us because we don’t know the actual rows which need to be converted into columns.
Related Post:
ReplyDeletehttp://www.itdeveloperzone.com/2011/04/columns-to-rows-in-sql.html