Help with Pivot

Posted on 2012-09-15
Last Modified: 2012-09-17
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
(select * from tmpProfile) S
      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.

Question by:sherbug1015
    LVL 25

    Expert Comment

    post your error

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

    Author Comment

    Here is the error

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

    Expert Comment

    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'

    should definitely work

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

    Expert Comment

    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.

    Author Comment

    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
    LVL 25

    Expert Comment

    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 ?

    Author Comment

    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
    LVL 25

    Expert Comment

    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
    LVL 25

    Accepted Solution

    bwt excel is capable of achieving that pivot pretty easily

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now