Dynamic sql statement to create table

Where whould I put the INTO tbl_UTILIZATION into the following statement?


Declare @strSQL varchar(max)            


DECLARE @cols varchar(2000)

-- Assigning Y here just for demo purposes, populate however you want. 
Declare @UTILIZED char(1) = 'Y'       

SELECT  @cols = STUFF(( SELECT DISTINCT             
                                '],[' + cast(Level3 as varchar)             
                        From PPS_SKU_UTILIZATION     
                        ORDER BY 1            
                        FOR XML PATH('')            
                      ), 1, 2, '') + ']'            
             
             
set @strSQL = 'select YEAR, MONTH, ' + @cols +           
' from (           
        select YEAR, MONTH, Level3, UTILIZED     
        from PPS_SKU_UTILIZATION
        WHERE UTILIZED=''' + @UTILIZED + ''') o           
pivot (COUNT(UTILIZED) for Level3 in (' + @cols + '))p
ORDER BY MONTH' 
 
exec(@strSQL)   

Open in new window

FairfieldAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
To create the table every time, to include a fail-safe DROP-CREATE in case the table already exists..
Declare @strSQL varchar(max)            


DECLARE @cols varchar(2000)

-- Assigning Y here just for demo purposes, populate however you want. 
Declare @UTILIZED char(1) = 'Y'       

SELECT  @cols = STUFF(( SELECT DISTINCT             
                                '],[' + cast(Level3 as varchar)             
                        From PPS_SKU_UTILIZATION     
                        ORDER BY 1            
                        FOR XML PATH('')            
                      ), 1, 2, '') + ']'            
             
IF OBJECT_ID('tbl_UTILIZATION') IS NOT NULL
   DROP TABLE tbl_UTILIZATION

set @strSQL = 'select YEAR, MONTH, ' + @cols +           
' INTO tbl_UTILIZATION
from (           
        select YEAR, MONTH, Level3, UTILIZED     
        from PPS_SKU_UTILIZATION
        WHERE UTILIZED=''' + @UTILIZED + ''') o           
pivot (COUNT(UTILIZED) for Level3 in (' + @cols + '))p
ORDER BY MONTH' 
 
exec(@strSQL) 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.