[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Help with Pivot

Posted on 2012-09-15
Medium Priority
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
  • 5
  • 3
LVL 27

Expert Comment

ID: 38401906
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

ID: 38401916
Here is the error

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

Expert Comment

ID: 38401969
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

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 12

Expert Comment

ID: 38402073
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

ID: 38402187
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 27

Expert Comment

ID: 38402226
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

ID: 38402370
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 27

Expert Comment

ID: 38403114
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 27

Accepted Solution

skullnobrains earned 1500 total points
ID: 38403115
bwt excel is capable of achieving that pivot pretty easily

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

834 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