2

I've been pulling my hair out for two days trying to put a MySQL query together to pull some data together. This is beyond my level of experience with SQL, however.

The following query pulls the data that I need, but I need to format it differently so I can use it. 

SELECT b.key, a.author, a.comment FROM action a, issue b 
WHERE a.issueid=b.id AND a.actiontype='comment';

This produces output like so:

1 | joe | comment 3
3 | sally | comment 2
1 | sam | comment 2
2 | julie | comment 2
1 | bill | comment 1
3 | joe | comment 1
2 | sam | comment 1

b.key is unique and can contain 0-∞ a.comment. There is a 1-1 relationship with the a.author and a.comment and I must maintain the original association. The output seems to be in ascending order based on the date of a.comment.

I would like to do get the data into the following format (CSV,) merging the a.author and a.comment results into a single column:

"1", "bill - comment 1", "sam - comment 2", "joe - comment 1"
"2", "julie - comment 1", "sam - comment 2"
"3", "joe - comment 1", "sally - comment 2"

I played around with several combinations of GROUP_CONCAT and ORDER and also messed with subqueries a bit based on this post and can't seem to get it formatted the way I need. Additionally, it seems like the order of the comment column changes. I need to maintain the original (oldest to newest) order, but my original query above produces results in newest to oldest order. If the delimiters pose a problem, I can certainly do some post processing with perl or awk search & replace on the results. Any help is greatly appreciated!

Community
  • 1
  • 1
David Theodore
  • 51
  • 3
  • Is it absolutely necessary to do it in MySQL? If not, you might find this much easier with a general-purpose language. – Matt Fenwick Feb 09 '12 at 01:30

2 Answers2

1

How about

SELECT
    issue.key,
    GROUP_CONCAT(CONCAT(action.author, ' - ', action.comment) ORDER BY action.created ASC SEPARATOR '|')
FROM action
INNER JOIN issue
    ON action.issueid = issue.id
WHERE action.actiontype = 'comment'
GROUP BY issue.key;

with some post processing to split based on the pipe character. If you get truncation issues you may need to increase the value of group_concat_max_len.

user1191247
  • 10,808
  • 2
  • 22
  • 32
0

Check this out. The join doesn't affect in any way how data is displayed so I'm writing the query as if your table is actually the one you display as the output

select concat('"', id, '", ',
  group_concat(concat('"', author, ' - ', comment, '"') separator ", ")
) as result from table1
group by id

The ordering of the group_concat is up to you.

Here is an example.

Hope this helps.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Thanks for the tip on adding characters into the results. '"' didn't work, but I used this concept to insert other characters that I can replace in post processing. – David Theodore Feb 09 '12 at 18:47
  • That's weird, beause the example shows exactly what you're looking for – Mosty Mostacho Feb 09 '12 at 18:50
  • I'm running this from mysql -e so it might be something to do with the way the shell interprets the " in the line. I need to post process the data to strip out line feeds anyway, so I can just replace the characters with " – David Theodore Feb 09 '12 at 18:55
  • Oh, you didn't mention that in the question. Yes, you should probably have to escape the " with \" – Mosty Mostacho Feb 09 '12 at 18:57