Pivot Table sap

[former_member509679]

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]

[former_member188586]

Hi

based on bellow Pivot you can write your Code

: SELECT SUM(SUM_ABC) FROM (SELECT

((Sum(T1.[Debit]) -Sum(T1.[Credit]))-(((SELECT isnull(sum (a.[DpmAppl] ),0)FROM [dbo].[ODPO]aWHEREa.cardcode=T1.[ShortName] and a.[TaxDate] <='20300101' AND a.[Project]=T1.[Project])) +

(SELECT isnull(sum ( a.[DpmAppVat]),0)FROM [dbo].[ODPO]aWHEREa.cardcode=T1.[ShortName] and a.[TaxDate] <='20300101' AND a.[Project]=T1.[Project] and a.vatsum>0)-

(SELECT isnull( sum ( (a.[DpmAppl]*b.[Rate]/100)),0)"Bal. To Recover" FROM [dbo].[ODPO]aINNER JOIN DPO5 b ON a.[DocEntry] = b.[AbsEntry]WHEREa.cardcode=T1.[ShortName] and a.[TaxDate] <='20300101' AND a.[Project]=T1.[Project]))

-

((SELECT isnull(sum (a.doctotal ),0)FROM [dbo].[Orpc]a INNER JOIN rpc1 c ON a.[DocEntry] = c.[DocEntry]WHEREa.cardcode=T1.[ShortName] and a.[TaxDate] <='20300101' AND a.[Project]=T1.[Project] and c.basetype=204))

) SUM_ABC

FROM OJDT T0INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId inner join OCRD T2 on T2.[CardCode] = T1.[ShortName] INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode WHERE T1.[Project] ='XYZ' andT1.[ShortName] like 'S%'and T0.[TransType] != 204 and T0.RefDate <= '20300101' and T3.[GroupName] = 'Suppliers' and T2.[CardName] not like 'Duplicate_%' GROUP BY T1.[ShortName], T2.[CardName], T1.[Project] )SUM_PQ where SUM_ABC<0

[jimmyl]

Hi Scott,

The PIVOT function in SQL should work for you. You may refer to the syntax here: PIVOT syntax.

Using PivotTables in Excel or the Cross-tab in Crystal Reports may handle this easier if there are too many Profit Codes in your case. Because you have to hard code your Profit Codes as column headers in SQL.

Hope it helps.

Jimmy