Can I use PIVOT for this Problem?

Question

Can I use PIVOT for this Problem?

sean_denney

SSC Enthusiast

Points: 162
More actions
December 22, 2009 at 12:36 pm

#141181

Hi, I can't figure out if I can use PIVOT for this or some other solution. I have:
create table survey(
Question varchar(200)
answer(varchar(200))
every survey someone fills out gets 1-n rows of, for example (1-n depending on which questions they answer)
insert into survey(question, answer) values ('email' , 'xxx@yyy.com')
insert into survey(question, answer) values ('favorite color' , 'red')
insert into survey(question, answer) values ('product preference' , 'product A')
...
I'm trying to create output that looks like
Email, favorite color, product preference...
Can I use PIVOT, given that there is no aggregation to be applied, and the "key" is email and is just another question/answer pair?
Anyone have any good ideas for how to get a list of emails and answers like this?
Any ideas or links appreciated! Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Your database design is all incorrect for something like that. You need more than one table. That way you can have more than one answer per user. And you would be able to store that perfectly with no problems.

You are making a simple task very, very difficult by trying to use only one table

Andrew SQLDBA

sean_denney SSC Enthusiast Points: 162 More actions · Answer 2

Believe me, I know! And I agree completely! I inherited this insane design, and now I have to figure out a way to extract this data until I can fix it. Pretty much every basic rule of database design has been broken. I haven't even mentioned the "comma seperated list-in one field" issue 🙂

Anyway, If anyone knows how I might accomplish this via sql it would be greatly appreciated.

Thanks

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

The only command that I know that will solve the problem is

DROP Database <DatabaseName>

Sorry to hear that, good luck with it. You may try pumping the data out into some other tables and query the data from there.

But no, PIVOT will not work for this. I do have some code that will take the comma separated and separate it out for you.

Andrew SQLDBA

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

sean_denney (12/22/2009)
I'm trying to create output that looks like
Email, favorite color, product preference...

You want these to act as columns of your resultset?

---------------------------------------------------------------------------------

LutzM SSC Guru Points: 107049 More actions · Answer 5

Is there any column available to know which rows belong together (even an ID column would be ok as long as all rows after one entry with 'email' would belong to the same "email entry group").

I would do it following those steps:

1) Assign a group number to each block after an "email" entry using the quirky update method. (link: follow me! [/url] )

2) extract the comma separated list into a separate table using s string split function (if you don't have one available yet, please see the "Tally Table" link in my signature. There is a description of a great and excellent performing solution.)

3) Create a DynamicCrossTab list to get the PIVOT like output as expected. See the related link my signature as well.

One thing to notice: All three articles I pointed you at are written by Jeff Moden. Coincidence? Most probably not... 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]