Dynamic Query UnPivoting:
Using Sys.Columns and Stuff(Dynamic Querying) to concatenate | grouping the multiple columns(need to be appended) values under a single column.
Example S1, S2, S3...... as SNO
Later dynamic query is processed with Unpivot to convert multiple column data to the single row.
Example D1, D2, D3..... under the column PDT with ID to map with the static data.
Dynamic queries results are stored in the Table variables for later processing.
Table variables created are used with CTE's to join Unpivoted data with static data to get the final result.
If OBJECT_ID('UnpivotData','u') is not null
Drop table UnPivotData
go
Create table UnPivotData
(
Id int,
BILLNO int,
Cus_Name varchar(10),
[Date] date,
Tot_Bal Int,
S1 smallint,
S2 smallint,
S3 smallint,
S4 smallint,
D1 varchar(3),
D2 varchar(3),
D3 varchar(3),
D4 varchar(3),
Q1 smallint,
Q2 smallint,
Q3 smallint,
Q4 smallint,
U1 smallint,
U2 smallint,
U3 smallint,
U4 smallint,
T1 smallint,
T2 smallint,
T3 smallint,
T4 smallint,
Total int,
CusCode varchar(2)
)
insert into UnPivotData
Select 29, 1, 'XXX', '03-04-2017', 1932, 1, 2, 3, NULL, 'AAA', 'BBB', 'CCC', NULL, 6, 30, 6, NULL, 80, 35, 67, NULL, 480, 1050, 402, 0, 1932, 'DF'
union
Select 40, 2,'YYY', '04-04-2017', 6454, 1, 2, 3, NULL, 'AAA', 'DDD', 'FFF', NULL, 30, 24, 50, NULL, 80, 96, 35, NULL, 2400, 2304, 1750, 0, 6454, 'MQ'
DECLARE @SNO AS NVARCHAR(MAX)='',@PDT AS NVARCHAR(MAX)='',@QTY AS NVARCHAR(MAX)='', @UNIT AS NVARCHAR(MAX)='', @TOT AS NVARCHAR(MAX)=''
SET @SNO = STUFF((SELECT distinct ',' + QuoteName(C.name)
from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'S'+'[0-9]%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @PDT = STUFF((SELECT distinct ',' + QuoteName(C.name)
from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'D'+'[0-9]%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @QTY = STUFF((SELECT distinct ',' + QuoteName(C.name)
from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'Q'+'[0-9]%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @UNIT = STUFF((SELECT distinct ',' + QuoteName(C.name)
from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'U'+'[0-9]%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @TOT = STUFF((SELECT distinct ',' + QuoteName(C.name)
from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'T'+'[0-9]%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
Declare @Query1 Nvarchar(MAX),@Query2 Nvarchar(MAX),@Query3 Nvarchar(MAX),@Query4 Nvarchar(MAX),@Query5 Nvarchar(MAX)
Set @Query1='select ID,SNO from
(
Select ID,'+@SNO+' from UnPivotData
)L
unpivot
(
SNo for col in('+ @SNO +')
) u'
Set @Query2='select ID,PDT from
(
Select ID,'+@PDT+' from UnPivotData
)L
unpivot
(
PDT for col in('+ @PDT +')
) u'
Set @Query3='select ID,QTY from
(
Select ID,'+@QTY+' from UnPivotData
)L
unpivot
(
QTY for col in('+ @QTY +')
) u'
Set @Query4='select ID,UNIT from
(
Select ID,'+@UNIT+' from UnPivotData
)L
unpivot
(
UNIT for col in('+ @UNIT +')
) u'
Set @Query5='select ID, TOT from
(
Select ID,'+@TOT+' from UnPivotData
)L
unpivot
(
TOT for col in('+ @TOT +')
) u'
Declare @t1 table
(
id int,
SNO Smallint
)
Declare @t2 table
(
id int,
PDT Varchar(3)
)
Declare @t3 table
(
id int,
QTY Smallint
)
Declare @t4 table
(
id int,
UNIT Smallint
)
Declare @t5 table
(
id int,
TOT Smallint
)
Insert into @T1 execute ( @Query1 )
Insert into @T2 execute ( @Query2 )
Insert into @T3 execute ( @Query3 )
Insert into @T4 execute ( @Query4 )
Insert into @T5 execute ( @Query5 )
;With Cte
as
(
Select UD.Id,BILLNO,Cus_Name,[Date],Tot_Bal,SNO,Row_Number() over(partition by T1.ID Order by T1.ID)Row_NO--,PDT,QTY,UNIT,TOT
,Total,CusCode
from UnPivotData UD join @t1 T1 on UD.Id=T1.id
)
,
Cte1
as
(
Select Cte.*,PDT from Cte Join
(Select *,Row_Number() over(partition by ID Order by ID)Row_NO from @t2) T2 on Cte.ID=T2.ID and Cte.Row_NO=T2.Row_NO
)
,
Cte2
as
(
Select Cte1.*,QTY from Cte1 Join
(Select *,Row_Number() over(partition by ID Order by ID)Row_NO from @t3) T3 on Cte1.ID=T3.ID and Cte1.Row_NO=T3.Row_NO
)
,
Cte3
as
(
Select Cte2.*,UNIT from Cte2 Join
(Select *,Row_Number() over(partition by ID Order by ID)Row_NO from @t4) T4 on Cte2.ID=T4.ID and Cte2.Row_NO=T4.Row_NO
)
,
Cte4
as
(
Select Cte3.*,TOT from Cte3 Join
(Select *,Row_Number() over(partition by ID Order by ID)Row_NO from @t5) T5 on Cte3.ID=T5.ID and Cte3.Row_NO=T5.Row_NO
)
Select Id,BILLNO,Cus_Name,[Date],Tot_Bal,SNO,PDT,QTY,UNIT,TOT
,Cuscode
from Cte4
