0

I'm having trouble with dynamic pivot. I tried to follow this expample: SQL Server : dynamic pivot over 5 columns but not getting results I want.

Data looks like below

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

With this Query

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;

I got result, which is correct

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

But what I really need is dynamic pivot (there are multiple Calclevels and CalcObjects). So the result should be like

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]

I tried this query below, but got nowhere so far. Is it possible to get result I described with dynamic pivot?


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)
HarryD
  • 37
  • 4

1 Answers1

1

Here you are

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)
Mageorphen
  • 36
  • 2