7 Replies Latest reply: Jun 22, 2015 11:28 AM by Alex Piccolo RSS

    SQL :scripting scalar variable problem (dymanic sql pivot)

    Alex Piccolo

      I get the following error when I try to create a qlik table from a SQL dynamic pivot:  I can't seem to find a source or example for below.    I think I need to create a intermediary table before loading scalar variable but I am not sure where?

       

      Thanks,

       

      ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Must declare the scalar variable "@query".


      Below is example of code that I am using:


      PivotTable:

      load *;

       

      SQL

       

      ( SELECT

                        columns1, columns2, column3...

        --  into #temptable

                    from

                        sqltable source

       

      DECLARE @cols AS NVARCHAR(MAX),

          @query  AS NVARCHAR(MAX);

       

      select @cols = STUFF((SELECT ',' + QUOTENAME(column2)

                          from #temptable

                          group by column2

                          order by column2

                  FOR XML PATH(''), TYPE

                  ).value('.', 'NVARCHAR(MAX)')

              ,1,1,'')

       

       

      set @query = 'SELECT columns1,' + @cols + ' from

      (

      select

      max(T.columns1) as columns1

      ,isnull(T.      [column2],0)as       [column2]

      ,COALESCE(T.      [column3],0)as       [column3]

       

       

         from #temptable T

         group by T.column2, T.column3

         )  x

       

       

                  pivot

                  (

                      sum(column3)

                      for column2 in (' + @cols + ')

                  ) p '

       

       

      execute(@query);

      drop table #temptable;