获取动态生成的数据透视表到临时表中 [英] Getting a Dynamically-Generated Pivot-Table into a Temp Table

问题描述

我已经看过这，所以我知道如何创建具有动态生成的字段集的数据透视表。我现在的问题是，我想把结果放到一个临时表。

I've seen this, so I know how to create a pivot table with a dynamically generated set of fields. My problem now is that I'd like to get the results into a temporary table.

我知道为了从 EXEC 语句将结果集设置到临时表中，您需要预定义临时表。在动态生成数据透视表的情况下，没有办法预先知道这些字段。

I know that in order to get the result set into a temp table from an EXEC statement you need to predefine the temp table. In the case of a dynamically generated pivot table, there is no way to know the fields beforehand.

我可以想到的唯一的方法是获得这种类型的功能，使用动态SQL创建永久表。是否有更好的方法？

The only way I can think of to get this type of functionality is to create a permanent table using dynamic SQL. Is there a better way?

推荐答案

您可以这样做：

-- 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
    ')

$ b b

EDIT：添加动态sql以接受parquest到openquery

addded dynamic sql to accept params to openquery

这篇关于获取动态生成的数据透视表到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！