Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL rows to columns

Posted on 2010-09-11
10
Medium Priority
?
462 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_
  • 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 2000 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

578 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