understanding the pivot function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

understanding the pivot function

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-10-07 : 11:55:26

hello

I've read a bit about pivot function and get how it works with aggregate functions but I am wondering if it can work with raw data.... allow me to explain

lets say I have threetables.

First one is called questions

Its format it like this

Questions
questionid
question

answers
answerid
questionid
value (essentially the answer to the question column in question table)

questiongroups
questionid
questiongroupid

responses
responseid
answerid
responsefromserver

Now I have been asked to run a query which shows all the mobile phone numbers and email address for this database

obviously i could present it as raw data and run a filter to show only where q.question = 'email' or q.question = 'hometel'

however how could i pivot the data to show that as columns instead of rows

so essentially it would look like this

first column being the responseid, second column being home tel, then third being email

a bit of background to database essentially the front end had 300 questions, and it puts the 300 answers to these in the table and then creates a single responseid so basically im grouping by responseid as there are 300questionsid and 300answerid but only one responseid

hope this makes sense.

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-10-07 : 11:55:42

I mean four tables