[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Where statement in dynamic sql

I have the following code in a dynamic sql statement.  I need some help where to put a "where" statement.  I only want to include where the year = 2013 and the month = 3.  Where should I include this parameter in the query?


Declare @strSQL varchar(max)            
DECLARE @cols varchar(2000)            
      
          
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
        
             
) o           
pivot (COUNT(UTILIZED) for Level3 in (' + @cols + '))p' 
 
exec(@strSQL)

Open in new window

0
Fairfield
Asked:
Fairfield
1 Solution
 
SharathData EngineerCommented:
Declare @strSQL varchar(max)            
DECLARE @cols varchar(2000)            
      
          
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 [year] = 2013 and [month] = 3
             
) o           
pivot (COUNT(UTILIZED) for Level3 in (' + @cols + '))p' 
 
exec(@strSQL)

Open in new window

0
 
PortletPaulCommented:
Sweaty Feet Will Give Horrible Odours

Select
From
Where
Group by
Having
Order by
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now