Concatentate data from a query into a single row (Transpose)

Submitted by shane on Thu, 2010-04-15 06:00

Let's say you have a query that returns this

cert_id
---------
100
200
300
500
600

What I want is to have it return this in a query

cert_id
----------
100,200,300.500,600

After much searching on the topic of transpose, with some complex solutions offered, I stumbled onto the GROUP_CONCAT function in Mysql.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_...

So my query looks like this 

        /* Show Gift Cert nubmers */
        (
          SELECT GROUP_CONCAT(cert_id) FROM ugiftcert_history uh
          WHERE so.entity_id = uh.order_id
          GROUP BY uh.order_id
        ) AS gc_numbers,

Onto greatness.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <drupal5>, <drupal6>, <javascript>, <php>, <sql>. Beside the tag style "<foo>" it is also possible to use "[foo]".

More information about formatting options