order by in pivot

Question

how to use order by creditdebit.id desc in the given query..

 

declare @expenseNames nvarchar(max)='';

--select @expenseNames ='[Building_EXPENSES], [eLECTRIC]'

--select @expenseNames= case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

--from expenses

(select @expenseNames +=

+'['+Expenses_Name+']'

+','

FROM dbo.Expenses)

set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))

declare @dynamicSQL nvarchar(max)='';

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID order by CreditDebit.ID asc

)src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt '

EXECUTE(@dynamicSQL)

print @dynamicSQL

end

select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

)src

pivot

(

sum(Expenses)

for Expenses_Name In ([Electric_Expenses],[Miscelleneous_Expenses],[Lift_Expenses],[Generator_Expenses])

)as pvt