Solved

Dynamic T SQL help

Posted on 2013-06-26
4
332 Views
Last Modified: 2013-06-29
Hi I have a requirment to pass the value to Pivot as a column:

The expected query is below:

SELECT
        *
 FROM (
 SELECT distinct [ReadingDateKey], storekey ,   meter
 from [TechnicalBureau].[FactSubMeterReadings]
      
        ) up
PIVOT ( count( meter) FOR  ReadingDateKey IN  ("20130619","20130620","20130621","20130622","20130623","20130624")  ) AS pvt

My Dymic TSQL statement:

DECLARE @ReadingDateKey varchar(200)
DECLARE @sql varchar(1000)
DECLARE @RDT varchar(1000)

drop table #ReadingDateKey

 CREATE TABLE #ReadingDateKey
(
  ReadingDateKey  varchar(8)
)

 
INSERT INTO #ReadingDateKey  (ReadingDateKey)
SELECT  DISTINCT  CAST(ReadingDateKey as varchar)
FROM [TechnicalBureau].[FactSubMeterReadings]
WHERE ReadingDateKey between  
                              Datepart(year,  dateadd( dd,-8, getdate()))*10000
                        +   Datepart(Month, dateadd( dd,-8, getdate()))*100
                        +   Datepart(Day,   dateadd( dd,-8, getdate()))
                        AND Datepart(year,  dateadd( dd,-1, getdate()))*10000
                        +        Datepart(Month, dateadd( dd,-1, getdate()))*100
                        +   Datepart(Day,   dateadd( dd,-1, getdate()))  
-- Creating Column Names for Pivot


SELECT @ReadingDateKey = COALESCE( @RDT +',', '') +
 CAST(ReadingDateKey  as varchar)
from #ReadingDateKey
select   @ReadingDateKey
 SET @sql =  '
SELECT
             *
             FROM (
                  SELECT [ReadingDateKey], storekey ,   meter
                         from [TechnicalBureau].[FactSubMeterReadings]
                        
           ) up
                  PIVOT ( count(meter) FOR  ReadingDateKey IN  ('+ @ReadingDateKey+')  ) AS pvt'

PRINT @sql
EXEC (@sql)

The above dynamic query will  give me an error, becouse it does not pass the double quote to a values  in the IN clause (")

The query result is below:


(6 row(s) affected)

(1 row(s) affected)

SELECT
             *
             FROM (
                  SELECT [ReadingDateKey], storekey ,   meter
                         from [TechnicalBureau].[FactSubMeterReadings]
                        
           ) up
                  PIVOT ( count(meter) FOR  ReadingDateKey IN  (20130619,20130620,20130621,20130622,20130623,20130624)  ) AS pvt
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '20130619'.

Any help on how to pass the "" to the values?

Thanks in advance
0
Comment
Question by:keplan
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
in your code:

SELECT @ReadingDateKey = COALESCE( @RDT +',', '') +
 CAST(ReadingDateKey  as varchar)

perhaps try:

SELECT @ReadingDateKey = COALESCE( @RDT +',', '') +
'"' +  CAST(ReadingDateKey  as varchar) + '"'

i.e. add quotes to each ReadingDateKey
0
 

Author Comment

by:keplan
Comment Utility
Here is the answer:

DECLARE @ReadingDateKey varchar(200)
DECLARE @sql varchar(1000)




 CREATE TABLE #ReadingDateKey
(
  ReadingDateKey  varchar(8)
)

 
INSERT INTO #ReadingDateKey  (ReadingDateKey)
SELECT  DISTINCT  CAST(ReadingDateKey as varchar)
FROM [TechnicalBureau].[FactSubMeterReadings]
WHERE ReadingDateKey between  
                              Datepart(year,  dateadd( dd,-8, getdate()))*10000
                        +   Datepart(Month, dateadd( dd,-8, getdate()))*100
                        +   Datepart(Day,   dateadd( dd,-8, getdate()))
                        AND Datepart(year,  dateadd( dd,-1, getdate()))*10000
                        +        Datepart(Month, dateadd( dd,-1, getdate()))*100
                        +   Datepart(Day,   dateadd( dd,-1, getdate()))  
-- Creating Column Names for Pivot


SELECT @ReadingDateKey = COALESCE( @ReadingDateKey +',', '') +
 + '"' + CAST(ReadingDateKey  as varchar) +'"'
from #ReadingDateKey
 SET @sql =  '
SELECT        *
             FROM (
                    SELECT DISTINCT [ReadingDateKey],d.[Name],   meter
                    from [TechnicalBureau].[FactSubMeterReadings] f
                    JOIN   [Fault].[DimStores]      d
                    ON(f.storekey = d.storekey )
                    AND d.[EffectiveEnd] is null
           ) up
                  PIVOT ( count(meter) FOR  ReadingDateKey IN  ('+ @ReadingDateKey+')  ) AS pvt'

--PRINT @sql
EXEC (@sql)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 445 total points
Comment Utility
great! it's finished then?
0
 

Author Closing Comment

by:keplan
Comment Utility
g
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
query help 18 50
SQL Query Assistance 4 30
Script to randomly create characters in MS SQL 6 15
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

16 Experts available now in Live!

Get 1:1 Help Now