Hello,
Wondering if it is possible to create a pivot table using the below query
An example of the query is
Code Account Name Balance Profit Code
40000 Sales Revenue 10000 Sales
40000 Sales Revenue 10000 Office
40000 Sales Revenue 10000 Returns
What i am hoping for i a pivot table to display the each profit code in its own column
Example
Code Account Name Sales Office Returns
40000 Sales Revenue 10000 10000 10000
SELECT T0.[FormatCode], T0.[AcctName], SUM(T1.[Credit]-T1.[Debit]) Balance , T1.ProfitCode
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE T1.[RefDate] >= [%0] and T1.[RefDate] <= [%1] and T0.[ActType] in ('I','E')
and DateDiff(YY,T2.[RefDate],GetDate()) = 0
AND T1.[Debit] != T1.[Credit] GROUP BY T0.[FormatCode],
T0.[AcctName], T1.ProfitCode
Having SUM(T1.[Debit]-T1.[Credit]) != 0 ORDER BY T0.[FormatCode]