1

I have a dynamic query as shown below. The @ColumnNames param has multiple columns this pivot is using. @ID and @Apartment_ID come from insert parameters.

SET @DynamicSQL = 'select id, name, address, phone, remarks, ' + **@ColumnNames** + ' 
                   from  (select b.id, name, criteria_id, impact_value, remarks  
                          from dbo.User u
                          inner join dbo.ID b on b.id = u.id
                          where b.Instance_ID = '+ **@Id**  + 
                           'and ownerID in (select * from fnSplitString(''' +   **@Apartment_ID**  + + ''',' + ''','''       + '))'              
              + ') as t  
              pivot (max(impact_value) for criteria_id in (' + **@ColumnNames**+')
              ) pivoted '

Exec sp_executesql @DynamicSQL

will be get a result as shown in the screenshot. The columns (91, 92,..) are not fixed that get from @ColumnNames:

image description here

I want to insert this dynamic result set into temp table to make sorts function. 

Declare @SQLstrs nvarchar(max)

IF OBJECT_ID('tempdb..#tempResult') IS NOT NULL
    DROP TABLE #tempResult  

CREATE TABLE #tempResult 
(  
     id int,
     name nvarchar(max),
     address nvarchar(max),
     phone nvarchar(max),
     Remarks nvarchar(max),
     **@ColumnNames**
)

--EXEC (@Alter_sql);

SET @SQLstrs = 'Insert into #tempResult ' + @DynamicSQL 

EXEC @SQLstrs

Since temp table need to include the fixed columns, how can I set up the dynamic columns that can't know how many columns will be insert ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Siamon
  • 83
  • 1
  • 13
  • 1
    What does this mean - *"I want to insert this dynamic result set into temp table to make sorts function."*? This sounds very much like [an xy problem](https://meta.stackexchange.com/q/66377/179361) to me. – GarethD Aug 21 '17 at 09:06
  • What exactly do you plan to do with `#tempResult` table then? – Vojtěch Dohnal Aug 21 '17 at 09:49
  • I want to make order by name from temp table cause I tried it before inserting order by statement inside the @DynamicSQL didn't work. Thats why, first put the dynamic data into temp table without sorting. Then making sorting with temp table – Siamon Aug 21 '17 at 10:11
  • You can add order by to Dynamic SQL - what do you mean by "didn't work"? Did you get an error? Did you not get the results you were expecting? It should be something like - `pivot (max(impact_value) for criteria_id in (' + @ColumnNames+')) pivoted ORDER BY pivoted.name, pivoted.address'` – GarethD Aug 21 '17 at 10:19
  • @GarethD thanks for suggest. But its still didn't work and showed error like, column name are invalid. – Siamon Aug 29 '17 at 03:10

1 Answers1

1

try to use select into 

 SET @SQLstrs = 'select * into #tempResult from(' + @DynamicSQL  +') as _temp'
prance
  • 21
  • 4
  • how can I mentioned about columns name before ? – Siamon Aug 21 '17 at 08:35
  • you do not need to create table #tempResult before,"select into " will create the table automatically – prance Aug 21 '17 at 08:38
  • Thanks. but I try to use this query but when exec the @SQLstrs , didn't show up the result table . as I m newbie please suggest how should I need to do ? thanks alot. – Siamon Aug 21 '17 at 08:47
  • It didn't show up the table because you did not ·select· it, After ·EXEC @SQLstrs· ,you should `select * from #tempResult` to see the result – prance Aug 21 '17 at 08:58
  • 2
    @prance, If you create the local temporary table dynamically using `SELECT * INTO #tempResult...` it will be out of scope for the parent session, you would have to use a global temporary table - `SELECT * INTO ##tempResult ...`. If you do this though, you encounter concurrency issues. – GarethD Aug 21 '17 at 09:07
  • Thanks for help. but it still didn't show up the same result as table from @DynamicSQL when select out the temp table :( – Siamon Aug 21 '17 at 09:12
  • As @GarethD mentioned ,try using global temporary table,to avoid concurrency issues,consider using `newID()` as part of the temporary table name – prance Aug 21 '17 at 09:34