Skip to Content
0
Jun 12, 2018 at 03:17 PM

Pivot Table sap

194 Views

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]