将动态生成的数据透视表放入临时表

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

添加动态 sql 以接受 openquery 的参数

addded dynamic sql to accept params to openquery

这篇关于将动态生成的数据透视表放入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！