Avatar of Zack
Zack
Flag for Australia asked on

SQL - Pivot table query

Hi EE,

I have this query:

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;

Open in new window


The line 
dateadd(month,12,@FromDate)

Open in new window

Works fine if i'm comparing 'months' but if I compare 'days' all get is a single column

Any assistance is welcome.

Thank you. 
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Zack

ASKER
HI Ryan,

Not sure what you mean can you clarify with example?

Thank you. 
Zack

ASKER
Hi Ryan,

I got it:

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), 10)  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(day,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;

Open in new window



Thank you 
Ryan Chong

emmm, I could see it wrongly. So disregard my first comment, but below a POC which should working fine, by applying:

dateadd(day,12,@FromDate)

Open in new window

to compare with dates.

Full test scripts:

declare @fromDate datetime = '2018-04-01', @cols varchar(100), @query nvarchar(max)

drop table if exists #TempTable

;with e as
(
    SELECT 100 BL, '2018-04-01' accountingdate, LEFT(CONVERT(VARCHAR, '2018-04-01', 120), 7) TransDate, 'A' company union
    SELECT 120 BL, '2018-04-15' accountingdate, LEFT(CONVERT(VARCHAR, '2018-04-15', 120), 7) TransDate, 'A' company
)
select * into #TempTable from e
WHERE e.accountingdate >= @FromDate and e.accountingdate < dateadd(day,12,@FromDate)

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;

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck