Solved

Dynamic T SQL help

Posted on 2013-06-26
4
337 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
ID: 39280157
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
ID: 39280184
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
ID: 39280188
great! it's finished then?
0
 

Author Closing Comment

by:keplan
ID: 39287636
g
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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