Solved

MySQL rows to columns

Posted on 2010-09-11
10
445 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 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
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)

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CheckListBox usage 3 71
Present Absent from working date rage 11 38
Optimize the query 5 43
Import MySQL data into MS Access using VB.Net interface 5 31
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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