IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL BEGIN DROP TABLE #TempTable END dECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) declare @fromDate datetime set @fromDate = '2018-04-01' 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(dayofyear,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;
dateadd(month,12,@FromDate)Works fine if i'm comparing 'months' but if I compare 'days' all get is a single column
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.