如何在连接必须为PIVOT的多个表时创建临时表 [英] how to create temp table while Joining multiple tables that have to be PIVOT
问题描述
我有类似的3-4个脚本，用于不同的表以动态地PIVOT，如先前发布 动态枢轴(行至列)
I have similar 3-4 scripts for different tables to dynamically PIVOT as previously posted Dynamic Pivot (row to columns)
目标: -创建一个excel文件，连接PIVOTed表中的所有字段(每个表中的所有行)
Goals: - Create a excel file Joining all the fields from the PIVOTed tables(all the rows from each table)
步骤:
-
为每个集合创建单独的临时表(在不同的表上旋转之后)
Create separate temp tables for each set (after Pivoting on different tables)
在列ID上联接所有临时表
JOIN all the temp tables on column ID
SELECT列(所有临时表)
SELECT columns from the resultset(all temp tables)
**想知道是否存在一种更好的方法来创建临时表，该过程使用将所有表联接在一起以进行最终选择的过程.
**Would like to know if there is a better way to create a temp table using a procedure for joining all the tables for the final select.
**我尝试创建临时表，但收到错误:无效的对象
**I tried creating temp table but got the error :invalid object
是上一篇文章中接受的答案的结果**
as a result of the accepted answer in the previous post**
INSERT into #T1 execute('execute' + @query )
select * from #T1
**
案例1:在您的桌子上旋转数据点
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ','
+ quotename('Instance'+ cast(instance as varchar(10))+'_'+c.name)
from yourtable t
cross apply sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('id', 'instance')
group by t.instance, c.name
order by t.instance
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select id,
''Instance''+cast(instance as varchar(10))+''_''+col col,
value
from
(
select id,
Instance,
Name,
cast(Size as varchar(50)) Size,
Tech
from yourtable
) x
unpivot
(
value
for col in (Name, Size, Tech)
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
--exec(@query)
我尝试创建临时表，但收到错误:无效的对象 由于上一篇文章中的答案被接受
I tried creating temp table but got the error :invalid object as a result of the accepted answer in the previous post
INSERT into #T1 execute('execute' + @query )
select * from #T1
案例2:MYtable的相同代码PIVOT
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ','
+ quotename('Instance'+ cast(instance as varchar(10))+'_'+c.name)
from mytable t
cross apply sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('id', 'instance')
group by t.instance, c.name
order by t.instance
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select id,
''Instance''+cast(instance as varchar(10))+''_''+col col,
value
from
(
select id,
Instance,
Name,
cast(Size as varchar(50)) Size,
Tech
from mytable
) x
unpivot
(
value
for col in (Name, Size, Tech)
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
INSERT into #T2 execute('execute' + @query2 )
select * from #T2
**情况3:OurTable的相同代码PIVOT **
** Case 3:Same code PIVOT for OurTable**
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ','
+ quotename('Instance'+ cast(instance as varchar(10))+'_'+c.name)
from ourtable t
cross apply sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('id', 'instance')
group by t.instance, c.name
order by t.instance
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select id,
''Instance''+cast(instance as varchar(10))+''_''+col col,
value
from
(
select id,
Instance,
Name,
cast(Size as varchar(50)) Size,
Tech
from ourtable
) x
unpivot
(
value
for col in (Name, Size, Tech)
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
INSERT into #T3 execute('execute' + @query2 )
select * from #T3
最终选择:
select * from #T1
inner join #T1.id=#T2.id
inner join #T1.id=#T3.id
推荐答案
部分问题是您正在使用动态sql，并且希望将其插入到临时表中以备后用.这是与之有关的问题:
Part of your problem is that you are using dynamic sql and you want to insert that into a temp table for use later on. Here are the issues with that:
- 无法创建临时表，因为列数未知.
- 在动态SQL内创建的临时表将超出范围供以后使用.当您创建本地临时表(以单个#号开头的临时表)时，就会存在此问题
如果要将这些多个表连接在一起，则可以创建一个全局临时表或一个实际表(非临时表)，这些表可以在动态SQL执行期间创建并在该范围之外使用.
If you want to join these multiple tables together than you could create a global temp table or a real table (not temp) that can be created in during the dynamic SQL execution and used outside of that scope.
使用 OP 中的代码，我用正在创建表:
Using the code from the OP I altered it with a table being created:
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ','
+ quotename('Instance'+ cast(instance as varchar(10))+'_'+c.name)
from yourtable t
cross apply sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('id', 'instance')
group by t.instance, c.name
order by t.instance
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
into ##t1 -- < create global temp table or real table without the ##
from
(
select id,
''Instance''+cast(instance as varchar(10))+''_''+col col,
value
from
(
select id,
Instance,
Name,
cast(Size as varchar(50)) Size,
Tech
from yourtable
) x
unpivot
(
value
for col in (Name, Size, Tech)
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
exec(@query);
select * from ##t1
请参见带演示的SQL小提琴.
这将使您可以将多个表连接在一起.
This will allow you to join the multiple tables together.
参考:
这篇关于如何在连接必须为PIVOT的多个表时创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
