Having a issue with to display the data in particular format

Question

I have a table with the data,

Item    sales_description   date        amount  sequence
----    -----------------   ----        ------  --------
 xyz    base cost           2013-03-31  2.50    1
 xyz    packing charges     2013-03-31  5.50    2
 xyz    miscellaneous       2013-03-31  1.50    3

 xyz    base cost           2013-06-30  3.50    1
 xyz    packing charges     2013-06-30  6.50    2
 xyz    miscellaneous       2013-06-30  1.00    3
 xyz    delivery charges    2013-06-30  5.00    4

 xyz    base cost           2013-09-30  5.00    1
 xyz    packing charges     2013-09-30  6.50    2
 xyz    labor charges       2013-09-30  3.50    3
 xyz    miscellaneous       2013-09-30  1.00    4
 xyz    delivery charges    2013-09-30  5.00    5

The output which I'm looking for is

Item    sales_description   date1       amount1     date2       amount2     date3       amount3
----    -----------------   -----       -------     -----       -------     -----       -------
 xyz    base cost           2013-03-31  2.50        2013-06-30  3.50        2013-09-30  5.00
 xyz    packing charges     2013-03-31  5.50        2013-06-30  6.50        2013-09-30  6.50
 xyz    labor charges       NULL        NULL        NULL        NULL        2013-09-30  3.50
 xyz    miscellaneous       2013-03-31  1.50        2013-06-30  1.00        2013-09-30  1.00
 xyz    delivery charges    NULL        NULL        2013-06-30  5.00        2013-09-30  5.00

I have tried writing a stored proc and storing the values in temporary table in the output format as above, and querying it from the temporary table, but unable to achieve it.

Is there any other way of getting the result. Any suggestion would be helpful.

thanks

do you always have 3 dates and 3 amounts, or there could be more? — fthiella, Jul 24 '13 at 15:58
Do you really have to get the dynamic number of columns in the result? you can try to `group by Item, sales_description` and `group_concat` the `amount` and `date` with sequence. You would be able to achieve that with a simple MySql query. — Slowcoder, Jul 24 '13 at 16:15
Do you really have one table with all those columns, with duplicate `sales_description`? That is an exclusive arc and will cause you problems; see http://stackoverflow.com/a/621891/22437 — Dour High Arch, Jul 24 '13 at 16:22
Consider handling issues of data display in the presentation layer (if you have one) — Strawberry, Jul 24 '13 at 17:04

Alden W. · Answer 1 · 2013-07-24T17:15:33.737

Here is a rough answer:

edit: I missed in this answer the part about the sequence number. Let me fix that.

SELECT t.Item, t.sales_descriptions, 
 ( SELECT sub1.date, sub1.amount FROM table AS sub1 ORDER BY sub1.date asc limit 1) AS date1,
 ( SELECT sub2.date, sub2.amount FROM table AS sub2 ORDER BY sub2.date asc limit 1,1) AS date2,
 ( SELECT sub2.date, sub2.amount FROM table AS sub2 ORDER BY sub2.date asc limit 2,1) AS date3,
  ...
 FROM table as t
 GROUP BY t.Item, t.sales_description;

Having a issue with to display the data in particular format

1 Answers1