Dynamic Pivoting, Using Coalesce and quotename in sql

COAlesce(expression n)
works same as isnull but
 returns highest precision value of not null value as result
also if all the value are null coalesce returns null value but isnull returns not null value.
coalesce esecutes more than once to get result
but isnull is a function and hence executes only ones to get result

QUOTENAME(ColumnName)
returns [ColumnName]
use: to get name of columns for pivoting.

example:
Declare @JVNO nvarchar(200)
select @JVNO=
COALESCE(@JVNO+', ','') +QuoteName(JVNo,'''')
from (select distinct JVNo     from tbl_incomejournaldetails inner join tbl_incomeJournalmaster on tbl_incomeJournalmaster .JournalVoucherID =tbl_incomejournaldetails .JournalVoucherID
inner join tbl_LedgerMaster on tbl_incomejournaldetails .LedgerMasterID=tbl_LedgerMaster .LedgerMasterID
where tbl_LedgerMaster .Code='Previous Year') as B
Order By B.JVNo
--print @JVNO

declare @sql nvarchar(max)
set @sql='select * from tbl_incomejournaldetails inner join tbl_incomeJournalmaster
on tbl_incomeJournalmaster.JournalVoucherID =tbl_incomejournaldetails.JournalVoucherID
where JVNo   in ('+@JVNO+')'
--print @sql
exec( @sql)

 Example 2:

Declare @Columns nvarchar(max)
Declare @SQL nvarchar(max)
select @Columns=
COALESCE(@Columns+', ','')+QUOTENAME (VillageID)
from (select VillageID from skhpvillage
) as M
Order By M.VillageID

Set @SQL='
with pivotdata as
(
select VillageID,
Monthid,ActualUnit
from skhpviewDemandBillDetail
)

select Monthid,'+@Columns+' from PivotData
Pivot
(
SUM(ActualUnit) for VillageID in (' + @Columns + ')
) as pivotresult
'
exec(@SQL)

Example 3:

  Create Table #TBL4
 (
 VoucherDate date,
 VoucherNo nvarchar(100),
 FirmName nvarchar(256),
 Amount float,
 INcomeID int
  )
Declare Row Cursor for
select distinct VoucherNo  from  View_Journal where View_Journal.IncomeHeadID is not null and (isnull(@VoucherFrom ,'')='' or VoucherDate >= @VoucherFrom ) and (isnull(@VoucherTo,'')='' or VoucherDate <=@VoucherTo );
open Row;
Fetch Next From row into @VoucherNo
    WHILE @@FETCH_STATUS = 0
    begin
   
 Declare IncomeHead Cursor for
 select IncomeHeadID  from Billing_IncomeHead ;
 open incomeHead;
 FETCH NEXT FROM IncomeHead into @IncomeHeadID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    if exists (select View_Journal.VoucherDate ,VoucherNo,FirmName   ,isnull((isnull(CrAmount,0)-isnull(DrAmount,0)),0) as Amount ,View_Journal.IncomeHeadID  from  View_Journal  where View_Journal.IncomeHeadID=@IncomeHeadID and View_journal.VoucherNo=@VoucherNo )
    begin
insert into #TBL4
   
     select top(1) View_Journal.VoucherDate ,VoucherNo,FirmName  ,isnull((isnull(CrAmount,0)-isnull(DrAmount,0)),0) as Amount ,@IncomeHeadID  from  View_Journal  where View_Journal.LedgerMasterID in (select Ledgermasterid from tbl_LedgerMaster where category ='Bank' ) and View_journal.VoucherNo=@VoucherNo
    end
    else
    begin
    insert into #TBL4 select (select distinct VoucherDate from View_Journal where VoucherNo=@VoucherNo ),
   
    @VoucherNo,
    (select distinct FirmName   from View_Journal where VoucherNo=@VoucherNo  ),
    0,@IncomeHeadID
    end
    FETCH NEXT FROM IncomeHead into @IncomeHeadID
    end
    Close incomehead
    deallocate incomehead
   
    Fetch Next From row into @VoucherNo
    end
   
     Close Row
     Deallocate Row

select @Columns=
COALESCE(@Columns+', ','')+QUOTENAME (Incomeid)
from (select distinct Incomeid from #TBL4) as B
Order By B.Incomeid

Set @SQL='
with pivotdata as
(
select
Incomeid,VoucherDate,VoucherNo,FirmName,Amount
from #TBL4
)

select VoucherDate,VoucherNo,FirmName,'+@Columns+' from PivotData
Pivot
(
SUM(amount) for Incomeid in (' + @Columns + ')
) as pivotresult
'
exec(@SQL)
drop table #TBL4