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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.