0

I am trying to figure out how to get (Joomla/Virtuemart) column values into separated columns.

Table: jos_vm_order_item.

MySql 5.0. (can't change it)

I have orders (order_id) and each order has items (order_item_id) from 1 to 3.

order_id    order_item_id
1329        1427
1329        1428
1330        1429

I would like to get order_item_id's into separated columns suchs as (order_item_id_1, order_item_id_2, order_item_id_3) as follows

[My objective]

order_id    order_item_id_1     order_item_id_2     order_item_id_3
1329        1427                1428
1330        1429

As shown, there is only 2 items in these two orders.

This is what I managed to get working. 

SELECT order_id, GROUP_CONCAT(CONVERT(order_item_id, CHAR(8)))
FROM jos_vm_order_item
GROUP BY order_id

order_id    order_item_id
1329        1427,1428
1330        1429

However, I need order_item_id's into separated columns [as 'My objective' illustrates].

I would really appreciate if someone could help me about this issue.

I have googled a Lot and I couldn't find out if I could use join for this or not. I can't figure out how to create new columns from the above data.

Best, Jan

Dr.
  • 1
  • 1
  • possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – kmas Sep 25 '13 at 08:19
  • kmas thanks! However, it's not really giving a proper answer. – Dr. Sep 25 '13 at 08:30
  • 1
    *Why* do you need the dataset returned in this way? Can't you perform this manipulation within the *presentation layer* of your application? It's a strange task to require of the database layer, especially since your application code cannot then know in advance the number of columns that will be returned in the resultset... – eggyal Sep 25 '13 at 08:41
  • I would agree that the data should not be displayed in that manner considering an unknown number of columns. – AdrianBR Sep 25 '13 at 09:25

0 Answers0