Saturday, June 13, 2015

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.

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.

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.

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.

1 comment:

  1. Related Post:
    http://www.itdeveloperzone.com/2011/04/columns-to-rows-in-sql.html

    ReplyDelete

Popular Posts