Разворот динамического запроса:
Использование Sys.Columns and Stuff (динамические запросы) для конкатенации | группировка значений нескольких столбцов (необходимо добавить) в один столбец.
Пример S1, S2, S3...... как SNO
Позже динамический запрос обрабатывается с помощью Unpivot для преобразования данных нескольких столбцов в одну строку. Пример D1, D2, D3... в столбце PDT с идентификатором для сопоставления со статическими данными.
Результаты динамических запросов сохраняются в переменных таблицы для последующей обработки.
Созданные табличные переменные используются с CTE для объединения несводных данных со статическими данными для получения окончательного результата.
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
person
JayaPrakash
schedule
02.06.2017
