Inserting the results set of SQL dynamic pivot query into temporary table

Question

0.00/5 (No votes)

See more:

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:

SQL

/****** 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);

Posted 30-Aug-17 2:40am

Member 11975764

Updated 1-Sep-17 7:45am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pedro Espinosa · Answer 1 · 2017-09-01T07:45:00

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. 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. Add the insert statement to the select query you already have

INSERT INTO ##TempTable
Select *
 From...

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.