MySQL Select query - Sorting data based on date

Question

I'm using MySQL. This is table name item_supplier

supplier_ID   Item_ID   Date                  Price    QTY
1             1         2012-01-01 00:00:00   500.00   2
1             1         2012-01-03 00:00:00   450.00   10
2             1         2012-01-01 00:00:00   400.00   5
3             1         2012-05-01 00:00:00   500.00   1

I need a select query showing a table something like this.

supplier_ID      2012-01-01   2012-01-03   2012-05-01   
1                500.00(2)    450.00(10)   null
2                400.00(5)    null         null
3                null         null         500.00(1)

or, at least,

supplier_ID      2012-01-01   2012-01-03   2012-05-01   
1                500.00       450.00       null
2                400.00       null         null
3                null         null         500.00

I hope someone can help me on this or give me a hint.

Not getting anything from your required result set. Can you explain in words what you want? — Somnath Muluk
– Somnath Muluk, Commented Jan 10, 2012 at 4:12

mathematical.coffee · Accepted Answer · 2012-01-10 04:42:19Z

1

If there aren't a finite number of dates that are known beforehand, then you can't do what you want in MySQL alone.

Your best bet is to get a table like:

+---------+------------+-------------+-------------+
| Item_ID | Date       | supplier_ID | price       |
+---------+------------+-------------+-------------+
|       1 | 2012-01-01 |           1 | 500.00 (2)  |
|       1 | 2012-01-01 |           2 | 400.00 (5)  |
|       1 | 2012-01-03 |           1 | 450.00 (10) |
|       1 | 2012-05-01 |           3 | 500.00 (1)  |
|  ...    |    ...     |     ...     |    .....    |

Which can be done with:

SELECT Item_ID,Date,supplier_ID,CONCAT(FORMAT(Price,2),' (',QTY,')') AS price 
FROM item_supplier 
ORDER BY Item_ID,Date,supplier_ID;

Then on the C# side, loop through the results and print your desired output. Since the output is now sorted by Item_ID, Date, and then supplier_ID, it's simple to loop through the results and then output in the format you want.

answered Jan 10, 2012 at 4:42

mathematical.coffee

57.1k15 gold badges160 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SiHyung Lee Over a year ago

I guess the way i illustrated wasn't clear. There are FINITE number of dates that are known beforehand, for both column and row. Your suggestion was very helpful, but i hope there is another way only using MySQL.

Manoj Savalia · Accepted Answer · 2012-01-10 04:32:00Z

0

First of My SQL does not support to Crosstab/Pivot Query. So you need to create Dynamic temp Table for Columns and then inset record into it. Like, First you have to fetch all date in one cursor and then create temp table and insert columns based on date's Cursor . After Creating table create another cursor for inserting rows. and fetch every row and update temp table.I have also done this using this way.... If you have any query please contact.

answered Jan 10, 2012 at 4:32

Manoj Savalia

1,4023 gold badges13 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySQL Select query - Sorting data based on date

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related