从未知的编号和列名（从动态PIVOT查询中）插入临时表 [英] INSERT INTO temp table from unknown number and name of columns (from dynamic PIVOT query)

问题描述

我有一个动态查询，如下所示。 @ColumnNames 参数具有此数据透视表使用的多个列。 @ID 和 @Apartment_ID 来自插入参数。

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 

将得到结果，如屏幕截图所示。从 @ColumnNames 获得的列（91、92 ..）不固定：

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

我想将此动态结果集插入到临时表进行排序功能。

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 ?

推荐答案

尝试使用 select进入

 SET @SQLstrs = 'select * into #tempResult from(' + @DynamicSQL  +') as _temp'

这篇关于从未知的编号和列名（从动态PIVOT查询中）插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！