CODEWITHSUNDEEP
c#asp.netmysql
SiHyung Lee
SiHyung Lee

Reputation: 349

MySQL Select query - Sorting data based on date

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.

Upvotes: 0

Views: 1289

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56915

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.

Upvotes: 1

Manoj Savalia
Manoj Savalia

Reputation: 1402

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.

Upvotes: 0

Related Questions