Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
When you use OLE DB connection, you should use "?" instead of "@" or change your connection to ODBC.
If you ask why, because OLE DB uses positional parameters.
Thanks
Thanks I will give it a try.
Sent from Outlook<http://taps.io/outlookmobile>
Hi, Unfortunately that didn't work,
I changed to the @ to ? and still got same error?
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;
Ok. Let's try another way.
Now, keep '@', but use ODBC not OLE DB.
BTW, is this query running fine in SQL Server?
Works fine in sql;
Never used ODBC connection so I am newbie there; I am connecting via server credentials and not sure how to do that, is there good source?
You can set up an ODBC connection. Do a search in Windows for ODBC and I use 64 bit. Here are screenshots:
I would suggest picking "SQL Server Native Client 10.0" from the driver list.
Give it a name and find your server from the server list.
In terms of authentication, use "Windows authentication":
And then choose your default database:
After all this, test your connection to see if you can connect to it successfully.
You can also do a Google search for setting up ODBC connections and I am sure there are videos on YouTube.
Hope this helps.
Your very helpful; I will try this tomorrow as I used pivot without dynamic to meet a deadline; making this works will be very useful.
I am connecting via Sense not qlikview so you screenshots not that useful; I will research when I have more time later this week and respond appropriately with my results.
thanks
Though this is an old thread, I'm wondering if you ever found a resolution to this. I am getting an error when I run the PIVOT operator in SQL.