Create table variable from pivot results

Question

Is it possible to create table variable from pivot results?

My pivot query:

SELECT @QUERY = 'SELECT USER_KEY, ' + @COLDEPSUMMARY + '
                FROM CAUSDE_TAS                 
                PIVOT
                (
                SUM(USDE_HSU)
                FOR DEPA_KEY IN (' + @COLDEPARTMENTS + ')               
                ) PIVOT_LOCATIONS                   
                WHERE USDE_DAT >= ''' + format(@dDateFrom, 'MM.dd.yyyy') + ''' AND USDE_DAT <= ''' + format(@dDateTo, 'MM.dd.yyyy') + ''' 
                AND USER_KEY IN (' + @USERS_STR + ')
                GROUP BY USER_KEY'

@COLDEPSUMMARY AND @COLDEPARTMENTS are dynamically generated and are looking like this (there is around 70 columns):

@COLDEPSUMMARY:

SUM([120000003]),SUM([120000002]),SUM([140000001]),SUM([120000005]), ...

@COLDEPARTMENTS:

[120000003],[120000002],[140000001],[120000005], ...

Main reason why I want to create table variable from pivot table is that number of columns in pivot is dynamic - it can vary and there are a lot of columns (around 70).

UPDATE

As Jeremy suggested I've included INTO #tmp in dynamic query, so it looks like this:

SELECT @QUERY = 'SELECT USER_KEY, ' + @COLDEPSUMMARY + '
                INTO #tmp
                FROM CAUSDE_TAS                 
                PIVOT
                (
                SUM(USDE_HSU)
                FOR DEPA_KEY IN (' + @COLDEPARTMENTS + ')               
                ) PIVOT_LOCATIONS                   
                WHERE USDE_DAT >= ''' + format(@dDateFrom, 'MM.dd.yyyy') + ''' AND USDE_DAT <= ''' + format(@dDateTo, 'MM.dd.yyyy') + ''' 
                AND USER_KEY IN (' + @USERS_STR + ')
                GROUP BY USER_KEY'

If I just run the query with EXECUTE(@query), report is saying that more than 200 rows are affected. But, query:

select * from #tmp

is returning:

Invalid object name '#tmp'.

If I extract dynamic query and run it manually, all is good - #tmp is created and I can query it. (I extracted query with SELECT @query. Than I've just copy-pasted that selection into another window).

Dynamic query after extraction looks like this:

   SELECT USER_KEY, SUM([120000003]),SUM([120000002]),SUM([140000001])
   INTO #tmp
   FROM CAUSDE_TAS              
   PIVOT
   (
   SUM(USDE_HSU)
   FOR DEPA_KEY IN ([120000003],[120000002],[140000001])
   ) PIVOT_LOCATIONS                    
   WHERE USDE_DAT >= '09.19.2016' AND USDE_DAT <= '03.18.2017' 
   AND USER_KEY IN (100000002,100000004,100000006,100000008)
   GROUP BY USER_KEY

I don't understand why #tmp is not created if I just run EXECUTE(@Query)?

will it work to just add "INTO #tmp" between "SELECT" and "FROM"? then query from the #tmp table? see http://stackoverflow.com/questions/20107827/insert-data-into-temp-table-with-query — Jeremy, Feb 16 '17 at 16:45
@Jeremy - is there a problem if several users are running query at the same time? (so #tmp will be created with each query execution)? — FrenkyB, Feb 16 '17 at 16:49
#temp tables are per user session, so even if muliple users are running, they will not collide. ##tmp tables are global, and you would have collisions if you use that kind. — Jeremy, Feb 16 '17 at 16:55
@Jeremy - thanks a lot for answer. The only problem I have now is how to query #tmp? Query Select * from #tmp is giving me 'Invalid object name '#tmp'.' — FrenkyB, Feb 16 '17 at 16:59
it is outside the scope of the dynamically executed SQL, so put your select inside the string. If you had created the temp table outside the string, you could insert into it, but your columns are dynamic. depending on what you want to do with it, this may not be a great choice. Dynamic sql comes with a host of issues. see: http://www.sommarskog.se/dynamic_sql.html — Jeremy, Feb 16 '17 at 18:29

Create table variable from pivot results

0 Answers0