CODEWITHSUNDEEP
sql-serverpivot
xTernal
xTernal

Reputation: 327

selecting sql pivot table

I am trying to insert pivoted table which is done in a string into a temp table, #temp. the original code is as follow.

declare @list as varchar(max)
declare @sql varchar(max)
SELECT @list = COALESCE(@list + ',[' + cast(rank as varchar) + ']', '[' + cast(rank as varchar)+ ']')
from (
  select distinct rank
  from #pp
  where INSTR_ROLE = 'PI'
) x

set @sql ='select distinct * from (SELECT DISTINCT A.EMPLID AS EMPLID, 
                ''Yes'' AS prime, 
                (A.SUBJECT + '' '' +  A.CATALOG_NBR) AS Class, 
                rank
      FROM #pp A
      WHERE (A.INSTR_ROLE = ''PI'')
) as s
pivot ( max(class)
    for rank in ('+@list+')
) as pvt'

exec(@sql)

My method of creating the table is by specifying select into #temp, but when i exec, it say #temp table was not found. is there a way to achieve this?

set @sql ='select distinct * into #temp from (SELECT DISTINCT A.EMPLID AS EMPLID, 
                ''Yes'' AS prime, 
                (A.SUBJECT + '' '' +  A.CATALOG_NBR) AS Class, 
                rank
      FROM #pp A
      WHERE (A.INSTR_ROLE = ''PI'')
) as s
pivot ( max(class)
    for rank in ('+@list+')
) as pvt'

Upvotes: 0

Views: 39

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

The temp table created inside a dynamic query cannot be accessed outside of Dynamic Query.

The Dynamic query will have a different session from current session where the dynamic query is executed. So when you try to select the temp table ouside of dynamic query where the temp table created inside dynamic query it will throw error saying temp does not exist.

But when you try to select the temp table inside dynamic query it will work.

set @sql ='select distinct * into #temp from (SELECT DISTINCT A.EMPLID AS EMPLID, 
                ''Yes'' AS prime, 
                (A.SUBJECT + '' '' +  A.CATALOG_NBR) AS Class, 
                rank
      FROM #pp A
      WHERE (A.INSTR_ROLE = ''PI'')
) as s
pivot ( max(class)
    for rank in ('+@list+')
) as pvt;

select * from #temp'

If the query is not going to be executed parallel in different sessions you can use global temp table instead of temp table.

Global temp tables can be accessed outside of dynamic query even though it is created inside dynamic query.

 set @sql ='select distinct * into ##temp from (SELECT DISTINCT A.EMPLID AS EMPLID, 
                ''Yes'' AS prime, 
                (A.SUBJECT + '' '' +  A.CATALOG_NBR) AS Class, 
                rank
      FROM #pp A
      WHERE (A.INSTR_ROLE = ''PI'')
) as s
pivot ( max(class)
    for rank in ('+@list+')
) as pvt'

select * from ##temp

Upvotes: 2

Related Questions