need to pivot the query

[former_member646404]

hello experts,
i have query i want to change the rows into columns. can anyone convert this query using pivot operator?

i want *[AcctName]* and their Debits AND Credits accounts as column.

SELECT T0.Number, T0.[RefDate],t0.ref1, T2.[Credit],T2.[Debit],T3.[AcctName],T2.LineMemo,
T3.[FormatCode],T0.Memo , T0.Project
FROM OJDT T0
left JOIN JDT1 T2 ON T0.[TransId] = T2.[TransId]
left JOIN OACT T3 ON T2.Account = T3.AcctCode
where .[RefDate] between '01.01.20' and '06.30.20'

[kothandaraman_nagarajan]

Hi,

Try this query,

Select [AcctName] As AcctName, [Doc] As 'Doc#', [F] as 'FormatCode',[M] As 'memo', [P] as 'Project', [1] ,[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] from ( SELECT T0.Number as Doc, month(T0.[RefDate]) as Month,t0.ref1,SUM(T2.[Debit]-T2.Credit) as 'Amt',T3.[AcctName] as AcctName,T2.LineMemo, T3.[FormatCode] as F,T0.Memo as M , T0.Project as P FROM OJDT T0 left JOIN JDT1 T2 ON T0.[TransId] = T2.[TransId] left JOIN OACT T3 ON T2.Account = T3.AcctCode where T0.[RefDate] between '01.01.20' and '06.30.20' Group By T0.Number,t0.ref1, T3.[AcctName] ,T2.LineMemo, T3.[FormatCode],T0.Memo , T0.Project,T0.[RefDate] ) S Pivot (SUM(Amt) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

Regards,

Nagarajan

[former_member335325]

can you please share the table information and what you want in the form of table.