将SQL动态数据透视查询的结果集插入临时表 [英] Inserting the results set of SQL dynamic pivot query into temporary table

问题描述

如何在全局临时表中插入动态t-sql查询的结果，其中列未修复？请参阅下面的表定义，值插入和用于数据透视查询的t-sql。我需要将结果集插入到全局临时表中，以便我可以使用它来引用另一个查询。



我尝试过：

How can I insert into global temporary table the results of the dynamic t-sql query in which columns is not fixed? See below for the table definition, value insertion and the t-sql for pivot query. I need to insert the results sets to global temporary table so that I can use it to refer with another query.

What I have tried:

/****** Object:  Table [dbo].[ProdOrders]    Script Date: 30/8/2017 7:24:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProdOrders](
    [ItemCode] [nvarchar](50) NULL,
    [ReleasedDate] [date] NULL,
    [PlanQty] [float] NULL,
    [ActualQty] [float] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[ProdOrders]
           ([ItemCode]
           ,[ReleasedDate]
           ,[PlanQty]
           ,[ActualQty])
     VALUES
    ('0D203-22882-079','2017-08-18',12654,4218),
    ('0D203-22882-079','2017-08-23',15758,5253),
    ('0D203-22882-079','2017-08-27',26263,8754),
    ('0D203-22882-079','2017-09-02',7354,2451),
    ('0D301-05422-079','2017-08-18',31144,10381),
    ('0D301-05422-079','2017-08-18',20612,6871),
    ('0D301-05422-079','2017-08-23',25765,8588),
    ('0D301-05422-079','2017-08-29',19582,6527),
    ('0D301-05422-079','2017-09-04',15459,5153),
    ('0D203-22882-079','2017-09-22',5232,1744),
    ('0D203-22882-079','2017-09-28',13236,4412),
    ('0D203-22882-079','2017-10-03',7693,2564),
    ('0D301-05422-079','2017-09-23',24735,8245),
    ('0D301-05422-079','2017-09-27',19561,6520),
    ('0D301-05422-079','2017-09-06',23755,7918),
    ('0D301-05422-079','2017-09-14',23755,7918),
    ('0D301-05422-079','2017-09-17',29694,9898),
    ('0D203-22882-079','2017-11-01',2263,754),
    ('0D203-22882-079','2017-10-21',15693,5231),
    ('0D203-22882-079','2017-10-20',15968,5323),
    ('0D203-22882-079','2017-10-25',10521,3507),
    ('0D301-05422-079','2017-10-21',23755,7918),
    ('0D301-05422-079','2017-10-29',17816,5939),
    ('0D301-05422-079','2017-11-01',15612,5204),
    ('0D301-05422-079','2017-10-03',20816,6939),
    ('0D301-05422-079','2017-10-11',15612,5204),
    ('0D301-05422-079','2017-10-18',26020,8673)

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ItemCode
              ,B.*
         From  [dbo].[ProdOrders] A
         Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                             ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                     ) B (Item,Value)
      ) S
 Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);

推荐答案

您好，



您也可以动态创建全局临时表。以下代码应该在你的动态变量中（@SQL）



1.检查全局临时表是否存在，如果确实存在，则删除它，因为列可能插入数据时不匹配

Hi,

You can dynamically create the global temp table as well. The following codes should be inside your dynamic variable (@SQL)

1. Check if the global temporary table exists, if it does then drop it, because the columns might not match when inserting the data

if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

2.创建全局临时表与您在数据透视表中使用的相同Stuff代码

2. Create the global temporary table with the same "Stuff" code you are using in the pivot table

create table ##TempTable([ItemCode] nvarchar(50) null, ' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') + ' float null'
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') + ' float null'
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')+ ')

3.添加insert语句到您已经拥有的选择查询

3. Add the insert statement to the select query you already have

INSERT INTO ##TempTable
Select *
 From...

4.执行查询，只需选择全局临时表



最终查询应如下所示：

4. Execute the query and the just do a select to the global temporary table

The final query should look something like this:

Declare @SQL varchar(max) = '

if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

create table ##TempTable([ItemCode] nvarchar(50) null, ' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') + ' float null'
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') + ' float null'
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
Select *
 From (
        Select A.ItemCode
              ,B.*
         From  [dbo].[ProdOrders] A
         Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                             ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                     ) B (Item,Value)
      ) S
 Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);

SELECT * FROM ##TempTable

希望这有帮助。

Hope this helps.

这篇关于将SQL动态数据透视查询的结果集插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！