Solved

Dynamic sql statement to create table

Posted on 2013-10-24
1
211 Views
Last Modified: 2013-10-24
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

0
Comment
Question by:Fairfield
1 Comment
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39597733
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now