MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jon Frisby Date:October 19 2005 9:02pm
Subject:RE: Turning tables on their side
View as plain text  
You want a Pivot Table.  Excel will do this nicely (assuming you have
65536 rows or less), but SQL does not provide a mechanism to do this.
If you want a web based interface you can look at Jtable.  (I *think*
that's what it's called -- it's a Java web app that provides an HTML
pivot table interface...)

-JF
 

> -----Original Message-----
> From: Jeffrey Goldberg [mailto:jeffrey@stripped] 
> Sent: Wednesday, October 19, 2005 12:24 PM
> To: mysql@stripped
> Subject: Turning tables on their side
> 
> I suspect that this is the wrong list for this kind of 
> question, but if someone could point me to appropriate 
> sources, I would very much appreciate it.
> 
> I am new to SQL but inherited project designed by someone who 
> doesn't seem answer his email anymore.
> 
> Essentially date were collected on the web using PHP 
> inserting things into a MySQL data base.  It took me time, 
> but I now have a handle on what is in which of the 15 tables involved.
> 
> Each response to each question by each respondent produced 
> its own record (row).  That is, I have something like
> 
> 
>       respondent_id    question_id  answer_id  answer_text
>       ----------------------------------------------------
> 
>        23               201          56         NULL
>        23               202          20         NULL
>        23               203           1         NULL
>        23               204        NULL         Arlington
>        24               201          52         NULL
>        24               202          21         NULL
>        24               203           0         NULL
>        24               204        NULL         Richmond
> 
> 
> and so on for other respondent_ids as well.
> 
> What I would like to get for my users is something that looks like
> 
> 
>   respondent_id   q201             q202             
> q203               ...
>    
> --------------------------------------------------------------
> ---------
>    23             text-for-ans56   text-for-ans20  text-for- 
> answer1   ...
>    24             text-for-ans52   text-for-ans21  text-for- 
> answer0   ...
> 
> 
> So instead of having a record for each response, I'd like to 
> have a single record for each respondent that shows all of 
> that respondents responses.
> 
> For someone who knows SQL this should be easy.  I suspect that a
> 
>   group by respondent_id
> 
> clause will play a role, but I just don't see it.
> 
> As I said, references to books or sites that I should learn 
> from would also be welcome.
> 
> -j
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
> 
Thread
Turning tables on their sideJeffrey Goldberg19 Oct
  • Re: Turning tables on their sideBrent Baisley19 Oct
    • Re: Turning tables on their sideJeffrey Goldberg19 Oct
    • Re: Turning tables on their sidesheeri kritzer19 Oct
      • Re: Turning tables on their sideJeffrey Goldberg20 Oct
RE: Turning tables on their sideJon Frisby19 Oct
RE: Turning tables on their sideJon Frisby19 Oct
  • Re: Turning tables on their sideJeffrey Goldberg20 Oct