Solved

Where statement in dynamic sql

Posted on 2013-10-23
2
228 Views
Last Modified: 2013-10-24
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
Comment
Question by:Fairfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39595771
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39596634
Sweaty Feet Will Give Horrible Odours

Select
From
Where
Group by
Having
Order by
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 27
SQL QUERY 3 33
How come this XML node is not read? 3 31
SQL Query Across Multiple Tables - Help 5 23
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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