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_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL 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/[EMAIL PROTECTED]
Re: Turning tables on their side
The person you inherited from formatted the data correctly in my opinion. With the existing format, you can index all the data with a minimum number of indexes and quickly compile results. It can scale to any number of questions without having to modify the underlying data structure. It can also easily answer queries like, who missed one or more questions? What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. The model you are envisioning would also be difficult to query to determine missed questions. I would use the presentation layer (i.e. Perl, PHP, Python, Ruby, Java, etc) to pivot the data for display. That's where you also add things like coloring to highlight errors or interesting information. On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote: 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_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL 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? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[mailed and posted] On Oct 19, 2005, at 3:34 PM, Brent Baisley wrote: The person you inherited from formatted the data correctly in my opinion. I agree. What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. I'm sorry that I didn't make the question clear. My goal is to export an MS-Excel file that looks like my target. I do not wish to change how things are done in the DB. The end-users will want a spreadsheet like that for doing their analysis. Not for queries. I'm using phpmyadmin which will do an Excel export of a table for me. I just need to create the temporary table long enough to do the export. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
I agree with Brent. One particular bit of SQL you may find helpful is this: concat(ifnull(a_id,),ifnull(a_text,)) concat with anything and a null value will produce a null value. That snippet of sql code will help you get one answer from the 2 the original database had. Unless there's ever an answer_id AND an answer_text, although the example doesn't support that. so what you want is for something like php to take the result of: select respondent_id,question_id,concat(ifnull(answer_id,),ifnull(answer_text,)) as answer from test order by respondent_id,question_id; (which, in your example, gets you:) +--+--+---+ | r_id | q_id | answer| +--+--+---+ | 23 | 201 | 56| | 23 | 202 | 20| | 23 | 203 | 1 | | 23 | 204 | Arlington | | 24 | 201 | 52| | 24 | 202 | 21| | 24 | 203 | 0 | | 24 | 204 | Richmond | +--+--+---+ and process each row -- compare the respondent_id to a variable to see if you're still on the same respondent, and use the question_id to put the answer (id or text) into a hash or array. -Sheeri On 10/19/05, Brent Baisley [EMAIL PROTECTED] wrote: The person you inherited from formatted the data correctly in my opinion. With the existing format, you can index all the data with a minimum number of indexes and quickly compile results. It can scale to any number of questions without having to modify the underlying data structure. It can also easily answer queries like, who missed one or more questions? What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. The model you are envisioning would also be difficult to query to determine missed questions. I would use the presentation layer (i.e. Perl, PHP, Python, Ruby, Java, etc) to pivot the data for display. That's where you also add things like coloring to highlight errors or interesting information. On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote: 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_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL 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? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning tables on their side
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:[EMAIL PROTECTED] Sent: Wednesday, October 19, 2005 12:24 PM To: mysql@lists.mysql.com 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_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning tables on their side
Create an Excel spreadsheet. Import the raw data, structured as-is, into a worksheet. Select all the relevant columns. Go to Data - Pivot Table and Pivot Chart Report. Click Finish. From the PivotTable Field List, drag the respondant ID into the box labeled Drop Row Fields Here, then drag question ID into the box labeled Drop Column Fields Here. Voila. -JF -Original Message- From: Jeffrey Goldberg [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 19, 2005 1:44 PM To: Brent Baisley Cc: mysql@lists.mysql.com Subject: Re: Turning tables on their side [mailed and posted] On Oct 19, 2005, at 3:34 PM, Brent Baisley wrote: The person you inherited from formatted the data correctly in my opinion. I agree. What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. I'm sorry that I didn't make the question clear. My goal is to export an MS-Excel file that looks like my target. I do not wish to change how things are done in the DB. The end-users will want a spreadsheet like that for doing their analysis. Not for queries. I'm using phpmyadmin which will do an Excel export of a table for me. I just need to create the temporary table long enough to do the export. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[posted only] On Oct 19, 2005, at 4:07 PM, Jon Frisby wrote: Create an Excel spreadsheet. Import the raw data, structured as-is, into a worksheet. Select all the relevant columns. Go to Data - Pivot Table and Pivot Chart Report. Click Finish. From the PivotTable Field List, drag the respondant ID into the box labeled Drop Row Fields Here, then drag question ID into the box labeled Drop Column Fields Here. Voila. Thank you so much. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[posted only] On Oct 19, 2005, at 3:48 PM, sheeri kritzer wrote: One particular bit of SQL you may find helpful is this: concat(ifnull(a_id,),ifnull(a_text,)) concat with anything and a null value will produce a null value. That snippet of sql code will help you get one answer from the 2 the original database had. Thank you. I can immediately see several places where that will come in handy Unless there's ever an answer_id AND an answer_text, although the example doesn't support that. Well, there shouldn't be any cases like that, but I'll can run a quick query to check. Thank you for all of your help. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
