asked on

Dynamic T SQL help

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

PortletPaul

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

keplan

ASKER

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)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keplan

ASKER