Pivot Into A Table

[kjv1611]

This may sound crazy to some of you more seasoned SQL folks than I, but in one particular report that I'm trying to make work, I would like to try to pivot some results into a temp table, and then use the temp table to pull in a couple other columns.

The pivot is dynamically generated. Pivots in SQL are not my favorite thing. It's like I one day just decided I don't like PIVOT in SQL, and sort of set my mind against really learning it.

That said, I have gotten it to work a couple of times.

So, anyway, I've got this dynamic PIVOT working, but I need to add a couple more fields to the dataset, so I can make it work in Reporting Services.

Here's what the dynamic PIVOT sql is doing: (It's not the first time I've dealt with a dynamic pivot, but I did grab someone else's idea online, and just modified for my table/field names, and it works beautifully):

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Category) 
            FROM #ForPivot c
            FOR XML PATH('), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,')

set @query = 'SELECT ReportMonth, ' + @cols + ' from 
            (
                select ReportMonth
                    , NumberWidgets
                    , Category
                from #ForPivot
           ) x
            pivot 
            (
                 max(NumberWidgets)
                for Category in (' + @cols + ')
            ) p '

execute(@query)

Is there not some way to tell SQL to dump PIVOT values in to a table? If so, can anyone help me key in on that... either for use in the dynamic sql above, or just with any plan PIVOT? Certainly, I can figure out how to make it work in the dynamic piece if I can do it with PIVOT without dynamic SQL.

Thanks in advance for any help

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

ok, nevermind. I just did a test on the PIVOT table usage, and was able to just select it into a temp table.

I used an example built for the default testing database from Microsoft - AdventureWorks, I think, and changed everything to temp table usage, and just setup some fake values. Here's the whole thing - so I just have to convert the INTO piece to the dynamic sql, which I do not think will be too difficult:

CREATE TABLE #Sales(
	SaleID int identity(1,1)
	,OrderDate datetime
	,SubTotal money
)
	
INSERT INTO #Sales SELECT '1/1/2014' ,200
INSERT INTO #Sales SELECT '2/1/2014' ,100
INSERT INTO #Sales SELECT '3/1/2014' ,25
INSERT INTO #Sales SELECT '4/1/2014' ,400
INSERT INTO #Sales SELECT '5/1/2014' ,100
INSERT INTO #Sales SELECT '6/1/2014' ,20
INSERT INTO #Sales SELECT '7/1/2014' ,120
INSERT INTO #Sales SELECT '8/1/2014' ,700
INSERT INTO #Sales SELECT '9/1/2014' ,350
INSERT INTO #Sales SELECT '10/1/2014' ,225
INSERT INTO #Sales SELECT '11/1/2014' ,80
INSERT INTO #Sales SELECT '12/1/2014' ,14.5

INSERT INTO #Sales SELECT '1/1/2014' ,175
INSERT INTO #Sales SELECT '2/1/2014' ,375
INSERT INTO #Sales SELECT '3/1/2014' ,.75
INSERT INTO #Sales SELECT '4/1/2014' ,115
INSERT INTO #Sales SELECT '5/1/2014' ,83
INSERT INTO #Sales SELECT '6/1/2014' ,85.2
INSERT INTO #Sales SELECT '7/1/2014' ,12.11
INSERT INTO #Sales SELECT '8/1/2014' ,75
INSERT INTO #Sales SELECT '9/1/2014' ,15
INSERT INTO #Sales SELECT '10/1/2014' ,12.75
INSERT INTO #Sales SELECT '11/1/2014' ,125
INSERT INTO #Sales SELECT '12/1/2014' ,5

SELECT * INTO #t
FROM (
  SELECT
    YEAR(OrderDate) [Year],
    MONTH(OrderDate) [Month],
    SubTotal
  FROM #Sales
) TableDate
PIVOT (
  SUM(SubTotal)
  FOR [Month] IN (
    [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
  )
) PivotTable

SELECT * FROM #t

Was this an embarrassing find? Yes, yes it was.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So, it seems like I'm getting the SELECT INTO a temp table to work with the above standard pivot, but not a dynamic pivot. Any ideas from anyone as to why?



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I've gotten to another step.. it seems to work so long as I SELECT into a Sandbox table, but not a temp table... so I may just try to work with the Sandbox table and get on with my life.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Alright, this may be the final solution for me for now. I tested, and am able to delete, create, and select from a Sandbox table with Reporting Services with no hiccups. Or at least in my 2 field test table with 1 value each.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So, all that said, can anybody tell me why Dynamic SQL does not like temp tables? I mean, are temp tables just not part of Dynamic SQL's click or what?

I guess there's probably a blog somewhere I can read about it - I'll see what I can find.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So I believe I've found the answer on the temp tables in dynamic SQL. So, they can work in Dynamic SQL, but they are written to a different scope than those created outside the dynamic SQL. I did not know this before, so it's good to know. So the 2 options I can think of at this point in time are:
1. Sandbox tables
2. Global temp tables (##TempTable)

And for my purposes, the Sandbox table seems to work just fine.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57