SQL - Syntax Query

IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL
BEGIN
DROP TABLE #TempTable
dECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
declare @fromDate datetime
 
set @fromDate = '2017-4-1'


 
select * into #TempTable from
(SELECT  LEFT(CONVERT(VARCHAR, e.ACCOUNTINGDATE, 120), 7)  as TransDate, e.SUBLEDGERVOUCHERDATAAREAID as company, m.MAINACCOUNTID as ID,  
(a.ACCOUNTINGCURRENCYAMOUNT)AS BL
  FROM GENERALJOURNALACCOUNTENTRY a
  JOIN GENERALJOURNALENTRY e  ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY
  join DIMENSIONATTRIBUTEVALUECOMBINATION davc on a.LEDGERDIMENSION = davc.RECID and e.[PARTITION] = davc.[PARTITION]
    join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID and davc.[PARTITION] = m.[PARTITION]
  JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD
  WHERE p.type =  1-- 0=Opening,1=Operating,2=Closing
 and e.accountingdate >= @FromDate and e.accountingdate < dateadd(month,12,@FromDate)
 )AS A
 
--select * from #TempTable
 
SELECT @cols = COALESCE(@cols + ',' ,'' ) + QUOTENAME(TransDate)
FROM #TempTable
GROUP BY QUOTENAME(TransDate)
Order by QUOTENAME(TransDate)
 
SELECT @query  = N'
SELECT * FROM #TempTable  PIVOT (SUM(BL) FOR TransDate
 IN (' + @cols +  N') ) AS pvt order by company'
 
EXEC sp_executesql @query;

Select allOpen in new window