SQL Server:多列的动态数据透视和列的序列号

Question

我在动态枢轴方面遇到了问题。我试着遵循这个例子：SQL Server：5列的动态透视，但没有得到我想要的结果。

数据看起来像低于

AccDate     Account  AccountDescription      Amount  VatValue    CalcLevel   CalcObject
2020-08-31  4101     Purchaces                33750      4725        First      8309
2020-08-31  4101     Purchaces                33750      4725       Second       110
2020-08-31  4101     Purchaces                33750      4725        Third       111
2020-08-31  2660     VAT                       4725         0         NULL       NULL

使用此查询

select AccDate, AccountDescription, Amount, VatValue, 'First' AS [CalcLevel1], 'Second' AS [CalcLevel2], 'Third' AS[CalcLevel3], '8309' AS [CalcObject1], '110' AS [CalcObject2], '111' AS [CalcObject3]
from
(    
SELECT

AccDate,
Account,
AccountDescription,
Amount,
VatValue,
CalcLevel
FROM MyTable WHERE CalcLevel IS NOT NULL
) a
pivot
(
MIN(CalcLevel) for Account in ([First], [Second], [Third], [8309], [110], [111])
) as pvt;

我得到的结果是正确的

AccDate        AccountDescription   Amount  VatValue    CalcLevel1  CalcLevel2  CalcLevel3  CalcObject1 CalcObject2 CalcObject3
2020-08-31     Purchaces            33750     4725         First      Second       Third         8309        110        111

但我真正需要的是动态枢轴(有多个Calclevels和CalcObjects(。所以结果应该像一样

AccDate        AccountDescription   Amount  VatValue    CalcLevel1  CalcLevel2  CalcLevel3  CalcLevel[n]    CalcObject1 CalcObject2 CalcObject3 CalcObject[n]
2020-08-31     Purchaces            33750     4725         First      Second       Third      result[n]        8309         110         111       result[n]

我在下面尝试了这个查询，但到目前为止没有结果。有可能得到我用动态枢轴描述的结果吗？

declare @sql nvarchar(max)
declare @cols nvarchar(max)
declare @sql nvarchar(max)
declare @cols nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(+col+CalcLevel)+','+QUOTENAME(col+CalcObject)
from mytable t
cross apply
(
select 'CalcLevel'
union all
select 'CalcObject'
) c (col)
group by col, CalcLevel, CalcObject
order by CalcLevel, CalcObject
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
select @cols
set @sql =
'SELECT  AccDate, AccountDescription, Amount, VatValue, ' + @cols + '
FROM
( select AccDate, AccountDescription, Account, Amount, VatValue,  CalcLevel,
col = CalcLevel+''_''+CalcObject+''_''+col, 
value
from mytable t
cross apply
(
select ''CalcLevel'', CalcLevel 
union all
select ''CalcObject'', CalcObject
) c (col, value)
) AS s
PIVOT
(
min(CalcLevel)
FOR Account IN (' + @cols + ')
) AS pvt
'
EXEC(@sql)

Answer 1

这是

create table MyTable 
(
accdate date,
account varchar(100),
accountdescription varchar(100),
amount bigint,
vatvalue int,
calcLevel varchar(100),
calcObject varchar(100)
)
insert into MyTable
values
('20200831', 4101, 'purchaces',33750,4725,'first','8309'),
('20200831', 4101, 'purchaces',33750,4725,'second','110'),
('20200831', 4101, 'purchaces',33750,4725,'third','111'),
('20200831', 2660, 'VAT',4725,0,null,null)
select * from MyTable
declare @sql nvarchar(max)
declare @cols nvarchar(max)
declare @colsLevel nvarchar(max)
declare @colsObject nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(calcLevel) +','+ QUOTENAME(CalcObject)
from mytable t

where calcLevel is not null       
group by CalcLevel, CalcObject
order by CalcLevel, CalcObject
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
select @cols
select @colsLevel = STUFF((SELECT ',''' + calcLevel + ''' as ' + QUOTENAME('CalcLevel'+convert(varchar(100), ROW_NUMBER() OVER(ORDER BY  CalcLevel asc)))
from mytable t                  
where calcLevel is not null       
group by CalcLevel
order by CalcLevel
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
select @colsLevel
select @colsObject = STUFF((SELECT ',''' + CalcObject + ''' as ' +QUOTENAME('CalcObject'+convert(varchar(100),ROW_NUMBER() OVER(ORDER BY CalcObject asc)))
from mytable t
where CalcObject is not null 
group by CalcObject
order by CalcObject
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
select @colsObject
set @sql =
'SELECT  AccDate, AccountDescription, Amount, VatValue, ' + @colsLevel + ', ' + @colsObject + '
FROM
( select AccDate, AccountDescription, Account, Amount, VatValue,  CalcLevel         
from mytable t
) AS s
PIVOT
(
min(CalcLevel)
FOR Account IN (' + @cols + ')
) AS pvt
'
print (@sql)
EXEC(@sql)