views:

450

answers:

1

Background:

I have this "with rollup" query defined in MySQL:

    SELECT      
        case TRIM(company)
            when 'apple'        THEN 'AAPL'
            when 'microsoft'    THEN 'MSFT'
            else '__xx__'
        END as company        

        ,case TRIM(division)
            when 'hardware'         THEN Trim(division)
            when 'software'         THEN Trim(division)
            else '__xx__'
        END as division

        ,concat( '$' , format(sum(trydollar),0))    as  dollars
    FROM        pivtest
    GROUP BY    
        company, division with rollup

And it generates this output:

AAPL;hardware;$279,296
AAPL;software;$293,620
AAPL;__xx__;$572,916
MSFT;hardware;$306,045
MSFT;software;$308,097
MSFT;__xx__;$614,142
__xx__;__xx__;$1,187,058

If you have used "with rollup" queries in MySQL before, you can most likely infer the structure of my source table.

Question:

Given this raw output of MySQL, what is the easiest way to get a "tree" structure like the following?

AAPL
    hardware;$279,296
    software;$293,620
        Total; $572,916
MSFT
    hardware;$306,045
    software;$308,097
        Total;$614,142
Total    
            $1,187,058
+2  A: 

Easiest is to do it in whatever client program you're using to receive and show the user MySQL's output -- definitely not easiest to implement presentation-layer functionality in the data layer!-) So tell us what language &c is in your client program and we may be able to help...

Edit: giving a simple Python client-side solution at the original asker's request.

With Python's DB API, results from a DB query can be most simply seen as a list of tuples. So here's a function to format those results as required:

def formout(results):
  marker = dict(__xx__='   Total')
  current_stock = None
  for stock, kind, cash in results:
    if stock != current_stock:
      print marker.get(stock, stock).strip()
      current_stock = stock
    if kind in marker and stock in marker:
      kind = ' '*8
    print '    %s;%s' % (marker.get(kind, kind), cash)

marker is a dictionary to map the special marker '__xx__' into the desired string in the output (I'm left-padding it appropriately for the "intermediate" totals, so when I print the final "grand total", I .strip() those blanks off). I also use it to check for the special case in which both of the first two columns are the marker (because in that case the second column needs to be turned into spaces instead). Feel free to ask in comments for any further clarification of Python idioms and use that may be necessary!

Here's the output I see when I call this function with the supplied data (turned into a list of 7 tuples of 3 strings each):

AAPL
    hardware;$279,296
    software;$293,620
       Total;$572,916
MSFT
    hardware;$306,045
    software;$308,097
       Total;$614,142
Total
            ;$1,187,058

The space-alignment is not identical to that I see in the question (which is a little inconsistent in terms of how many spaces are supposed to be where) but I hope it's close enough to what you want to make it easy for you to adjust this to your exact needs (as you're having to translate Python into PHP anyway, the space-adjustment should hopefully be the least of it).

Alex Martelli
yeah, i definitely was not planning to implement this in pure SQL. Presently, i am working with PHP.
dreftymac
OK, then I'm editing the question to tag it as php, so php experts can notice it and help (I could show you how to do it in, say, Python, but that wouldn't help much;-).
Alex Martelli
sure it would help, it would answer the question from a python perspective and give someone an example of how you'd solve the problem. translating out of python into php would be an extra step, but some folks dont mind that
dreftymac
Ok, editing my answer to supply Python code for it, then!
Alex Martelli
thanks Alex, that is a very readable and understandable implementation. An example in Python is just as good as (if not better than) an example in pseudo-code, so that definitely helps!
dreftymac
Glad to have helped!
Alex Martelli