extend a TSQL statement to loop results of a dynamic query

Question

The following SQL outputs a dynamic no of columns. The data is in the exact initial format for preparing a string to pass to a google chart. Problem is, I get nice output to management studio results (as a table). But now I want to loop these results and build a string to return as chart source..

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(MeterReadType)
                 FROM MeterReadView with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = ' SELECT MeterReadMonth as Month,  ' + @cols + ' 

FROM 
(
   SELECT MeterReadType, MeterReadMonth,isnull(total,0) as total
   FROM [MeterReadView] with (nolock)
) t
PIVOT
( 
  sum(total)
  FOR MeterReadType IN (' + @cols + ' )
) 
p ' ;

Execute(@query);

The above SQL outputs like...

Month   Desc 1, Desc 2, Desc 3, etc and so on.. 
2014-06 4       5       66
2014-06 9       3       7

But I need to loop results and build a string in the following format. I cannot just load into MVC because I need to specify a class and I don't know how many columns there will be, so i just build a string in SQL. The latter of which I can do. Just not sure how to extend this existing SQL to go into a loop because its execute(@query), how to get that into a temp table that knows the no of columns?

     [['Month','Desc 1','Desc 2','Desc 3'],
     ['Jun-14',4,5,66],
     ['Jun-14',9,3,7], 
['May-15',20,66,5]]

your `@cols` variable contains the list of all dynamic columns — cha, May 28 '15 at 04:41
I could use @Cols to get the first row for the string by concatenating that to the end of [Month]. then using replace to remove square brackets to apostrophe. However, to get the row data from 'Execute(@query);' i'm not sure — John, May 28 '15 at 05:08
May-14, 1,3,3 and other data is not match with your above output. is this something wrong. — Ajay2707, May 28 '15 at 05:22
Just sample pasted data to illustrate shape, sorry please ignore actual values, I tidied a little there — John, May 28 '15 at 05:27
your columns in the table view are presented by this query of yours: `SELECT distinct QUOTENAME(MeterReadType) FROM MeterReadView` — cha, May 28 '15 at 05:31
You can build a temp table on the fly, just by running a `SELECT Month, [Desc 1], [Desc 2] ... INTO #TempTable`, so you won't need to create the table previously, you just create it based on the number of coulmns your result set has. Is this what you're looking for? So, you can modify your `@query` to something like `SET @query = 'SELECT MeterReadMonth as Month, ' + @cols + ' INTO #tempTable FROM ...' ` — Radu Gheorghiu, May 28 '15 at 05:34
Yes, but it wants the temp table created first, i tried adding into #tempTable but says invalid object name temp table - ssurely I need to create that first, gr8 if it could just create since its tricky otherwise — John, May 28 '15 at 05:46
@John You don't have to create a temp table first, that's why it's a temp table. If you specify a hashtag before the table name, then it's available during that querying session. Take a look [**here for more details**](http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server). And if you use `INTO` then you don't need to worry about the number of columns your `@cols` variable holds. Sorry, worth mentioning that you need to generate a GLOBAL temp table for it to work, hence it should be `INTO ##tempTable`, otherwise you will get the `invalid object name` error. — Radu Gheorghiu, May 28 '15 at 05:49

score 0 · Answer 1 · edited May 23 '17 at 12:21

John I crated a sample for you, I don't have time to give the exact thing, but you understand the logic.

Logic is, you can store this output in temporary table at run-time( runtime means in dynamic query) , and then code as below.

EXECUTE ('SELECT * INTO #TEMP  FROM ' + @TableName +'; SELECT * FROM #TEMP;')

This links help you to create temp table in dynamic query.

TSQL select into Temp table from dynamic sql

Using a temporary table in dynamic sql in a stored procedure

Dynamic SQL results into temp table in SQL Stored procedure

--create table #t(month varchar(50) , [desc 1] int , [desc 2] int, [desc 3] int)

--insert into #t values ( '2014-06', 4,5,66 ) , ( '2014-06', 4,5,66 )

--select datename(month, getdate())
select datename(month, cast( month + '-01' as date)), 
* from #t

declare @month varchar(50), @desc1 varchar(50) , @desc2 varchar(50) , @desc3 varchar(50) , @str nvarchar(max) = '['
DECLARE Cur CURSOR FOR

        select *  from #t
        Open Cur

    Fetch Next from Cur INTO @month, @desc1, @desc2, @desc3
    While(@@fetch_status=0)                                                                                                                                                                                                     While(@@fetch_status=0)
    BEGIN
         select convert(char(3),cast( @month + '-01' as date) ,0),  @month, @desc1, @desc2, @desc3
          if(len(@str) > 1) set @str = @str + ', '
         set @str = @str + '[''' + convert(char(3),cast( @month + '-01' as date) ,0) + ''',''' + @desc1 + ''',''' + @desc1 + ''',''' + @desc1 + ''']'
    Fetch Next from Cur INTO @month, @desc1, @desc2, @desc3
    END
Close Cur
Deallocate Cur

set @str = @str + ']'
select @str

score 0 · Answer 2 · answered May 28 '15 at 06:40

You can use this query may be there is some syntax error in this query because you not provide the sample data please correct them this is without temp table .

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @colssplit AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(MeterReadType)
                 FROM MeterReadView with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @colssplit = STUFF((SELECT distinct 
                        ' + '',''+ CAST( ' +
                        QUOTENAME(MeterReadType) + ' AS Varchar(10))'
                 FROM MeterReadView with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query = ' SELECT ''[[''+''MOnth'','+@cols+']' +  ( SELECT (SELECT '+  '['''+  '[Month]' + ''',' + @colssplit + ' FROM ( select * from (   SELECT MeterReadMonth as Month,  ' + @cols + ' 

FROM 
(
   SELECT MeterReadType, MeterReadMonth,isnull(total,0) as total
   FROM [MeterReadView] with (nolock)
) t
PIVOT
( 
  sum(total)
  FOR MeterReadType IN (' + @cols + ' )
) 
p ) Mydata  for XML PATH ('') ,Type ).value('.', ''NVARCHAR(MAX)'') AS  ' ;

This looks promising. The issue I have is when I inserted into the temp table I created as part of my Execute(@Query) the temp table was out of scope. As if i'd then have to write the loop code in a built up string which is agony. I'm intrigued by this code, its dynamic on no of MeterReadTypes. But when I try it its got errors from the concatenation. Can this write the results out to a single string? Looks like its running another row select at the end — John, May 28 '15 at 07:27
@John could you please add your sample data `MeaterReadView` with schema so that I can fix the error . — Arun Gairola, May 28 '15 at 10:04

extend a TSQL statement to loop results of a dynamic query

2 Answers2