Solved

Dynamic T SQL help

Posted on 2013-06-26
4
339 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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