2

I have the following schema and sample data.

create table MyTable
(
    Id int,
    Year int,
    Channel varchar(10),
    Payments int
)

insert into MyTable values 
               (1,2012,'HV',100),
               (1,2014,'HV',56),
               (2,2012,'NL',17000)
               (2,2012,'HV',495), 
               (3,2013,'HV',565)

Now I want to create and insert dynamic pivot data in a temporary table. I am able to create the pivot data as the demo here.

But I want to store this data into a temporary table. What I have tried is as below.

Declare @SQL varchar(max) = '    
if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

create table ##TempTable([Id] int null, ' + 
            Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year)) + ' Varchar(20) null'            
            From [dbo].MyTable
            Order By 1 
            For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
Select *
 From (
        Select A.Id
              ,B.*
         From  [dbo].[MyTable] A
         Cross Apply ( values ( Id, Channel + CONVERT(Varchar(4), Year)
                     )) B (Item,Value)
      ) S
 Pivot (sum([Payments]) For Channel + CONVERT(Varchar(4), Year) in 
 (' + Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year))                                                               
                                               From [dbo].MyTable
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'

select @SQL

Exec(@SQL);

SELECT * FROM ##TempTable

It is giving me the following error.

Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '+'.

When printing the dynamic query it is giving the following result.

if object_id('tempdb..##TempTable') is not null  
begin      
    drop table ##TempTable  
end    
create table ##TempTable([Id] int null, [HV2012] Varchar(20) null,[HV2013] Varchar(20) null,[HV2014] Varchar(20) null,[NL2012] Varchar(20) null)  
INSERT INTO ##TempTable  
    Select *   From ( Select A.Id ,B.* From  [dbo].[MyTable] A 
        Cross Apply ( values ( Id, Channel + CONVERT(Varchar(4), Year) )) B (Item,Value) ) S   
        Pivot (sum([Payments]) For Channel + CONVERT(Varchar(4), Year) in ([HV2012],[HV2013],[HV2014],[NL2012]) ) p
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • 1
    Please add to your question what you get when you `PRINT(@SQL)` before executing it. – Tab Alleman Jan 25 '19 at 15:06
  • I'm betting it's `Channel + CONVERT(Varchar(4), Year)` in the pivot. I don't think you can do that. Since you are creating this from the `cross apply`, changing it to `[Value]` should work. Just something to try – S3S Jan 25 '19 at 15:10
  • @TabAlleman I have added dynamic query print output. – Suraj Kumar Jan 25 '19 at 15:14
  • I think maybe you need to make `Channel + CONVERT(Varchar(4), Year)` part of the dynamic calculation, and not have it still uncalculated in the `@SQL` variable. – Tab Alleman Jan 25 '19 at 15:31

1 Answers1

2

If you are using apply then why you need further same logic in PIVOT (i.e. Channel + CONVERT(Varchar(4), Year)) which is already available in apply.

So, i would use Value instead in PIVOT :

. . . 
Pivot (sum([Payments]) For [Value] in ([HV2012],[HV2013],[HV2014],[NL2012]) ) p,

So, your updated Dynamic SQL would be :

Declare @SQL varchar(max) = '    
if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

create table ##TempTable([Id] int null, ' + 
            Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year)) + ' Varchar(20) null'            
            From [dbo].MyTable
            Order By 1 
            For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
Select *
 From (
        Select A.ID, A.Payments
              ,B.*
         From  [dbo].MyTable a 
         Cross Apply ( values ( Channel + CONVERT(Varchar(4), Year)
                     )) B ([Value])
      ) S
 Pivot (sum([Payments]) For [Value] in 
 (' + Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year))                                                               
                                               From #tm
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'

print @sql

Exec(@SQL)

SELECT * FROM ##TempTable

I have made no of changes as there are many correction needs to be done prior to execution.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52