Pivot Table/Groupby/Sum question

[petr.jakes.tpc at gmail.com]

What about to let SQL to work for you.
HTH

Petr Jakes

Tested on Python 2.5.1
8<----------------------

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()

inputData=(
('Bob', 'Morn',  240),
('Bob', 'Aft', 300),
('Joe', 'Morn', 70),
('Joe', 'Aft', 80),
('Jil', 'Morn', 100),
('Jil', 'Aft', 150),
('Jil', 'Aft', 150),
('Jil', 'Aft', 150))

def data_generator(dataSet):
    for dataSetRow in dataSet:
        yield dataSetRow

pivotSelect='''
SELECT
NAME,
sum (AMOUNT) as TOTAL,
sum (case when (TIME_OF_DAY) = 'Morn' then AMOUNT else 0 END) as
MORN,
sum (case when (TIME_OF_DAY) = 'Aft' then AMOUNT else 0 END) as AFT
FROM MY_NAMES
GROUP BY 1'''

cur.execute("create table MY_NAMES(NAME, TIME_OF_DAY, AMOUNT)")
cur.executemany("""insert into MY_NAMES(NAME, TIME_OF_DAY, AMOUNT)
                values (?,?,?)""", data_generator(inputData))
cur.execute(pivotSelect)
for row in cur.fetchall():
    print row