SQL For Orders info report

[Tapper]

Hi

I'm creating orders reports and I wondered if there's SQL syntax that can do what I need. In the past I've exported the tables and used Excel to shift and match, but if there's a way to use SQL to match the data I need it would be preferable. In orders_total the data has multiple rows for each order number, and I need a report that has all of the info on one row.

Orders_total has the info like this:

orders_id##########_title####################text
444####################_Sub-Total:____$37.98
444####################_Flat Rate:##########$5.00
444####################_Total:##########___$42.98

I use the data like this:

orders_id##########_Sub-Total##########Flat Rate##########___Total
444####################_$37.98##########__$5.00####################_$42.98

I've tried a few orders handling mods and none of them provide all the info I need, so I've been building them by hand for a while now. I'd like to SQL this part of the report generation and free up some time.

Thanks in advance!

--Pat

[gharls]

Hello ...

Your optimal solution calls for an Open Source report writer (many are free of charge) ... that way, you could place your Order ID in a group header and then list the detail rows in any format you wish.

As an admittedly (blush) workaround SELECT hack (the hardwiring of the column names is surely inelegant at best), you could try the following SQL:

SELECT DISTINCT
a.orders_id,
(select text from orders_total b where class='ot_subtotal' and a.orders_id=b.orders_id) subtotal,
(select text from orders_total b where class='ot_flatrate' and a.orders_id=b.orders_id) flatrate,
(select text from orders_total b where class='ot_total' and a.orders_id=b.orders_id) total
FROM orders_total a

Good Luck ... and Long Live SQL!