Creating and Inserting data in Temporary table for Dynamic Pivoting Query Result.

In this Example I am going to show you how to Create a Temporary Table With no Columns Definition initially, That is Dynamically created Columns for the Temporary Table which will get its Definition and Data Dynamically from a Dynamic Pivot Query.

For This
1. We need To get the Columns for the Dynamic Pivoting in a variable (@Columns).
2. Define the Pivoting Query (@query) with 'select * into NewPivotTable' statement where NewPivotTable is the Resulting Table which we can use with in the Dynamic Query (@query) defination.

Note: The scope of the newly defined temporary table 'NewPivotTable' is with in the (@query)

Example:

DECLARE
   @Columns nvarchar( max
                    );
SELECT @Columns = COALESCE( @Columns + ', ' , ''
                          ) + QUOTENAME( PeriodName
                                       )
  FROM(
        SELECT DISTINCT PeriodName ,
                        PeriodCode
          FROM tblPeriodMaster
      )AS M
  ORDER BY M.PeriodCode;
DECLARE
   @query nvarchar( max
                  ) = 'SELECT * INTO NewPivotTable
FROM
(
  select p.PeriodName,(SUM(isnull(DrAmount,0))-SUM(isnull(CrAmount,0))) as Amount,LedgerName
from View_Journal J
inner join tblPeriodMaster P on (j.VoucherDate between p.StartDate and p.EndDate ) and p.FiscalYearID=2
inner join tbl_LedgerMaster L on L.Ledgermasterid=j.ledgermasterid
group by p.FiscalYearID,p.PeriodName,LedgerName
)t
PIVOT (SUM(amount) FOR PeriodName IN (' + @Columns + ') )
 as pvt;

 select * from NewPivotTable
 ';

EXECUTE ( @query
        );