asked on

Help with Pivot

I am trying to pivot a dataset, but I keep getting an error. I wonder could someone help me.

Here is the query

drop table #tmpquestions
create table #tmpquestions (shortdesc varchar(100))
insert into #tmpquestions select shortdesc from tmpprofile

DECLARE @SQLQuestions varchar(8000)

SELECT @SQLQuestions = @SQLQuestions + '
['+ shortdesc +']'
from #tmpQuestions

select locationid as fklocationid,shortdesc
From
(select * from tmpProfile) S
PIVOT
(Max(Response)
      FOR ShortDesc
      IN (@SQLQuestions))P
Group By LocationID

I am getting an error here: FOR ShortDesc
      IN (@SQLQuestions))P

Could someone help me solve this. I have attached the data to reproduce the error.

Thanks.
pivottable.xlsx

skullnobrains

post your error

most people do not have a test sql server at hand where they can inject random data to play with

sherbug1015

ASKER

Here is the error

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '@SQLQuestions'.

skullnobrains

you apparently cannot use a variable in a query part that you execute without using the EXECUTE statement

maybe you are aloud to use a variable inside the [...] but i'm unsure

you are definitely aloud to build the whole sql statement dynamically and execute it

something like

DECLARE @query NVARCHAR(4000)
SET @query = 'select whatever blah blah' + @SQLQuestions + 'rest of query'
EXECUTE(@query);

should definitely work

in your case, you probably do not need two separate queries for the @quey and @SQLQuestions

Jared_S

skullnobrains is right.
This will work, but you have to create a query string, and then execute that string.

You might find it useful to do something like

print (@query) prior to executing so you can make sure your command looks good.

sherbug1015

ASKER

What if @SqlQuestions is larger than 8K, which is my case. I am unable to use the dynamic string method because I have exceeded 8K

skullnobrains

hash the shortdesc and use the hashes may help. or rather use ids if possible
it will eventually still break at some point though

cant u use something bigger than 8k ? try using "max" ? using a text data type ?

maybe, you are trying to use pivot for something that could be retrieved easily in some other way. what is your actual need ?

sherbug1015

ASKER

On the file that I attached, look at the first tab called tmpProfile.

I need to pivot that dataset so that the locationid is the rowheader and shortdesc are the column headers and response is the value

skullnobrains

that is not the expression of a need. it is more like the expression of the way you are trying to achieve something else

it would be pretty easy for example to build a resultset where the shortdesc would be the row headers, max(response) a second column, and the locationids would be the values, or possibly all the possible responses would be in as many columns

it would be completely straightforwards to build a bidimentional array that would hold either shortdesc/locationid/response or possibly locationid/shortdesc/response

bottomline, is trying to build such a pivot table from your dataset in order to use it programmatically is rather useless, and trying to read it as a human being is most likely suboptimal. so i believe you need to feed the data in this specific format to an external tool, possibly after exporting it to some specific format that could easily be built programmatically

then this is completely off-topic and i believe we already answered your question. i'm ready to help though if you give the required information and/or some feedback about the suggestions i made in order to beat the 8k limit (if it actually does exist) and your current script

ASKER CERTIFIED SOLUTION

skullnobrains

membership

This solution is only available to members.

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

Start Free Trial