Solved

MySQL rows to columns

Posted on 2010-09-11
10
451 Views
Last Modified: 2012-05-10
I have the following data on a table cformsdata

id | sub_date | form_id | field_name | fieild_val

2342 | 9/8/2010 | 7 | First Name | Sharon
2342 | 9/8/2010 | 7 | Last Name | Smith
2342 | 9/8/2010 | 7 | Address | 1221 N 1222 E
2342 | 9/8/2010 | 7 | City | Cresco
2342 | 9/8/2010 | 7 | State | FL
2342 | 9/8/2010 | 7 | Postal Code | 52136
2342 | 9/8/2010 | 7 | Email | myemail@email.com
2343 | 9/9/2010 | 7 | First Name | Ben
2343 | 9/9/2010 | 7 | Last Name | Lopez
2343 | 9/9/2010 | 7 | Address | 2222 N 3333 W
2343 | 9/9/2010 | 7 | City | My City
2343 | 9/9/2010 | 7 | State | TX
2343 | 9/9/2010 | 7 | Postal Code | 12345
2343 | 9/9/2010 | 7 | Email | myemail2@email.com

My desired output is

sub_date | First Name | Last Name | Address | City | State | Postal Code | Email

9/8/2010 | Sharon | Smith | 1221 N 1222 E | Cresco | FL | 52136 | myemail@email.com
9/9/2010 | Ben     | Lopez | 2222N 3333 W | My City | TX | 12345 | myemail2@email.com

How can I write an SQL Query to display my desired output to the export to Excel?
0
Comment
Question by:Benjamin_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33655917

Hi Benjamin,
You may need to look at group_concat function.

Try this in mysql

SELECT GROUP_CONCAT (id, sub_date, form_id, field_name, fieild_val)
FROM cformsdata

Kj
0
 
LVL 8

Accepted Solution

by:
kingjely earned 500 total points
ID: 33655919
Hi,
sorry also, group by id

Try this.

SELECT GROUP_CONCAT (id, sub_date, form_id, field_name, fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655925
If you need the | in the fields, here you go..

SELECT GROUP_CONCAT (id, '  |  ' , sub_date, '  |  ' ,  form_id, '  |  ' , field_name, '  |  ' ,  fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Benjamin_
ID: 33655926
kingjely,

When I try to run that query on MySQL Workbench I get the following error:
Error Code: 1305
FUNCTION dbname.GROUP_CONCAT does not exist
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655937
I didn't realise you were using the workbench?
Ran it in Mysql Query Browser and it works?
Do you have access to Mysql query browser?
Then you can go File>export resultset to .Csv file

0
 

Author Comment

by:Benjamin_
ID: 33655941
kingjely,

I do have access to Query Browser but i got the same error.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655948
Wierd?
In query browser, If you go to HELP> ABOUT  
I have version 1.2.17 and it works fine for me.
What version do you have?

is this your table name 'cformsdata' ?
0
 

Author Comment

by:Benjamin_
ID: 33655953
I have the same version, and yes the table name is wp_cformsdata and that is the name I am using on the query.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655979
Ahh got it, it thinks GROUP_CONCAT is a field, because,
There is a space between GROUP_CONCAT  and  (cust_name, .. should be like this...

SELECT GROUP_CONCAT(id, '  |  ' , sub_date, '  |  ' ,  form_id, '  |  ' , field_name, '  |  ' ,  fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;

Change the | for (,) commas if you like and try to export results to .CSV

Also should work in workbench

let me know how you go, I have to go to work in 2 mins!  ...

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655986

TO get the fields you needed, something like this, you may need to adjust the title.. but the main point it GROUP_CONCAT..  Got to fly .. have fun Ben ;)

SELECT GROUP_CONCAT(sub_date, '  |  ' , First Name, '  |  ' , Last Name, '  |  ' , Address, '  |  ' , City, '  |  ' , State, '  |  ' , Postal Code, '  |  ' , Email) as  'sub_date,    |    , First Name,    |    , Last Name,    |    , Address,    |    , City,    |    , State,    |    , Postal Code,    |    , Email'
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 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