Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its relationship and is now hard to read and manipulate in Excel. Luckily, I did manage to keep 1 table which contains the important values. I now just need to re-format it for Excel to make it easier to work with.

Currently when I export from the DB to CSV the data looks like this:

id,lead_id,field_number,value
1,1,1,Mickey
2,1,2,Mouse
3,2,1,Minnie
4,2,2,Mouse

I would like to re-format this data into the following format:

id,lead_id,field_1,field_2
1,1,Mickey,Mouse
2,2,Minnie Mouse

I hope that makes sense, I wasn't sure how else to explain this question.

share|improve this question
    
Your output id columns don't make sense based on the matching input columns? And how many fields do you expect per grouping id? Just 2 based on field_id? –  gbn Oct 4 '11 at 0:11
    
There are more than 2 fields in the data set, and some leads have different amounts of overall fields. –  jnthnclrk Oct 4 '11 at 10:01
add comment

3 Answers

up vote 1 down vote accepted

This will generate a CSV per lead_id in order of field_number.

SELECT lead_id, lead_id, GROUP_CONCAT(value)
FROM test_table
GROUP BY lead_id
ORDER BY field_number
INTO OUTFILE '/tmp/result.txt'
  FELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

There are 2 lead_id columns to match your output. MySQL doesn't have ROW_NUMBER() to generate an arbitrary sequence.

I have found that the following works better to order the fields properly:

SELECT lead_id,
    GROUP_CONCAT(value
        ORDER BY field_number)
    FROM exp_wp_rg_lead_detail
    GROUP BY lead_id
share|improve this answer
    
Did you mean to reference lead_id twice in the first line? –  jnthnclrk Oct 7 '11 at 14:08
    
Also, I don't think ORDER BY field_number is working, because the concatenated values have seemingly random field orders across records. –  jnthnclrk Oct 7 '11 at 14:10
1  
I edited your answer to show how I fixed the ordering as the code didn't render very well in this comment box. –  jnthnclrk Oct 7 '11 at 14:27
add comment

I would do this in Excel - much easier!

Give the following assumptions...

  1. ALWAYS two items within the "field_number" field
  2. information sorted by "id,lead_id,field_number"
  3. Rename cells D1 and E1 as "field_1" and "field_2"

Then use the following formulae;

  1. In cell E2; "=D2 & ' ' & D3"
  2. In cell E3; "" (leave blank)

Then copy cells "E2:E3", and fill down through the rest of your data.

You should be left with something like this...

id,lead_id,field_number,field_1,field_2
1,1,1,Mickey,Mickey Mouse
2,1,2,Mouse,
3,2,1,Minnie,Minnie Mouse
4,2,2,Mouse

You then need to "copy and paste-special-values" the entire sheet.

Then filter the data and delete all those with a "field_number" = 2.

Then delete the column "field_number".

Hey-presto, all done, and should take no more than the length of time it took me to write up this answer!!! :)

Dave

share|improve this answer
    
Sorry, I should have said that there are more than 2 fields to deal with and that the overall amount of fields does vary. –  jnthnclrk Oct 4 '11 at 10:03
add comment

I'm guessing that your question is about output format???

If so, the answer is to properly format your SELECT syntax and use the 'INTO OUTFILE' modifier.

CSV Example from page:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.