How to Assign a Normal Table from a Dynamic Pivot Table

Dynamic Pivot Table - How to add the columns generated to another table?

I managed to get it working without the need for an extra table.

I was able to join two tables together and perform a PIVOT on just a select amount of the columns which before I didn't know you could do.

I understand the code below needs to be cleaned up a bit to stop possible SQL injection attacks, but I'll leave that till I get the whole program working.

SELECT @ColumnName = ISNULL(@ColumnName + ',','')
           +QUOTENAME(ChargeName)
    FROM  (SELECT DISTINCT ChargeName FROM ##chargesTable) AS ChargeName

     SET @DynamicPivotQuery =
    'SELECT MemberID, FirstName, Surname, CategoryName, TotalAmount, ' + @ColumnName +  
    ' FROM ##chargesTable
        PIVOT(MIN(ChargeAmount)
            FOR ChargeName IN (' + @ColumnName + ')) AS PivotTable
    ORDER BY MemberID'

    EXEC sp_executesql @DynamicPivotQuery

How can I make a dynamic pivot table inside a stored procedure that takes 12 months back of current date in a report?

I would be inclined not to use a dynamic pivot, and to handle the headers in your application code:

WITH Data AS
(   SELECT  DACP_ID,
            DACP_Value,
            [MonthNum] = 12 - DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP)
    FROM    T
    WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12
)   
SELECT  *
FROM    Data
        PIVOT
        (   SUM(DACP_Value)
            FOR MonthNum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
        ) pvt;

If you really need the Columns headers as they are then use:

DECLARE @Col NVARCHAR(MAX) = 
    (   SELECT  ', ' + QUOTENAME(CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - (12 - Number), 0), 112)) + ' = [' + CAST(number AS VARCHAR) + ']'
        FROM    Master..spt_values
        WHERE   Type = 'P'
        AND     number BETWEEN 0 AND 12
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)');

DECLARE @SQL NVARCHAR(MAX) = 
        N'WITH Data AS
        (   SELECT  DACP_ID,
                    DACP_Value,
                    [MonthNum] = 12 - DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP)
            FROM    T
            WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12
        )   
        SELECT  DACP_ID' + @Col + '
        FROM    Data
                PIVOT
                (   SUM(DACP_Value)
                    FOR MonthNum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
                ) pvt;';

EXECUTE SP_EXECUTESQL @SQL;

You can change the value 112 when converting a date to a varchar to various numbers to change the format of your date in the column headers (103 will give you dd/mm/yyyy as stated in the question).

---

SQL Fiddle (Shamelessly stolen from @bluefeet, who beat me to the answer). I've left this answer here though as it is slightly different as it will provide 13 columns (for the last year and current month) whether or not data exists for that month in the table.

SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

Getting a Dynamically-Generated Pivot-Table into a Temp Table

you could do this:

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
    select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
    select * from #t
    ')

EDIT: addded dynamic sql to accept params to openquery

Inserting the results set of SQL Dynamic Pivot Query into Temporary Table

The script below creates dynamically a global temp table. This script does not have a check to see if the table allready exists, you have to add that yourself.
Be carefull with using global temp tables. I think there are better alternatives to what you want.

    --Script for creating global temp table
    Declare @CreateTempTable [nvarchar](max) = '
    CREATE TABLE ##t ([ItemCode] nvarchar(50),' +
    stuff((Select distinct ',' + QuoteName(convert(varchar(6), ReleasedDate,112)+'-Plan') + ' float,' + QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual')  + '  float'
    From [dbo].[ProdOrders]
    Order By 1 For XML Path('')),1,1, '') + ')'

    Exec sp_executesql @CreateTempTable

    --Script for insert
    Declare @InsertSql [nvarchar](max) = '
    INSERT INTO ##t ([ItemCode], ' +
    stuff((Select distinct ','+QuoteName(convert(varchar(6), ReleasedDate,112)+'-Plan') + ', ' + QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual')  + ' '
    From [dbo].[ProdOrders]
    Order By 1 For XML Path('')),1,1, '') + ')'

    Declare @SQL varchar(max) = @InsertSql + '
    Select *
     From (
            Select A.ItemCode
                  ,B.*
             From  [dbo].[ProdOrders] A
             Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                                 ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                         ) B (Item,Value)
          ) S
     Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                                  +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                                   From [dbo].[ProdOrders]
                                                   Order By 1 
                                                   For XML Path('')),1,1,'')  + ') ) p'
    Exec(@SQL);

    Select * From ##t

Creating Pivot Table with Dynamic Range

You should avoid using Select and Activate as much as possible. I have modified your code below. You may want to reexamine the range values. You need to define the PivotCache and the PivotTable prior to creating.

Dim startCell As String
Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim PvtCache As PivotCache
Dim PvtTab As PivotTable

Set ws = Sheets("Details")

'Find Last row and column

lastRow = ws.Cells(ws.Rows.Count, 7).End(xlUp).Row
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(1, 7), ws.Cells(lastRow, lastCol)).Name = "DynamicRange"
Set ws2 = Sheets.Add(After:=ws)
ws2.Name = "PvtTable"

' Create Pivot Table

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("DynamicRange"))

Set PvtTab = PvtCache.CreatePivotTable(ws2.Cells(1, 1), "MyTable")

---

Related Topics