Create a pivot table using SQL

이글을 참고하면 좋다: http://stackoverflow.com/a/12599372
링크한 글에서는 MAX()를 사용하고 있는데 경우에 따라 SUM()이 필요할 수도 있을 것 같다.

SQLite 같은 프로그램은 @sql같은 것을 지원하지 않지만, query를 여러번에 나누어서 수행하면 된다.

아래 코드는 어떤 Django app에서 코드의 일부를 가져온 것이다. 긴 코드를 조금이라도 줄이기 위해 원래 코드의 일부를 삭제했고 테이블 이름이나 컬럼 이름도 간단하게 고쳤다. 그렇기 때문에 아래 코드가 실행이 안 될 수도 있다. 먼저, 아래 코드와 관련된 테이블 구조를 간단하게 설명하겠다.

• item table은 모든 item을 담고 있는 테이블이다. 각 item의 이름과 가격 등이 저장되어 있다.
• enabled_item_for_Z table은 어떤 Z에서 사용할 수 있는 item을 담고 있는 테이블이다. 이 테이블은 Z와 item을 foreign key로 가지고, 가격 등의 정보가 저장하고 있다. 가격을 중복으로 저장하는 이유는 Z에 따라 가격이 달라지는 상황을 고려했기 때문이다.
• request table은 user가 어떤 enabled_item_for_Z를 몇 개 신청했는지 저장하는 테이블이다. 이 테이블은 enabled_item_for_Z와 user를 foreign key로 가지고, 신청 수량 등을 저장하고 있다.

def some_func(request, myid):
    #### ...
    #### raw db connection
    cursor = connection.cursor()
    #### get available item list
    query_str_item = (
        'SELECT "item"."id", "item"."name"'
        ' FROM "item"'
        ' INNER JOIN "enabled_item_for_Z" ON '
        ' ("item"."id"'
        ' = "enabled_item_for_Z"."item_id")'
        ' WHERE "enabled_item_for_Z"."Z_id" = %s'
        ' ORDER BY "item"."id"'
    )
    cursor.execute(query_str_item, [myid])
    capsule_list = cursor.fetchall()
    #### make query string for selection
    aggregate_str = (
        '"SUM(CASE WHEN item_id=" || item.id'
        ' || " THEN request.{0}'
        ' ELSE 0 END) AS {1}"'
        ' || item.id AS {2}'
    )
    from_str = (
        ' FROM "item"'
        ' INNER JOIN "enabled_item_for_Z" ON '
        ' ("item"."id"'
        ' = "enabled_item_for_Z"."item_id")'
        ' WHERE "enabled_item_for_Z"."Z_id" = %s'
        ' ORDER BY "item"."id"'
    )
    inner_str = 'SELECT '\
            + aggregate_str.format('quantity', 'qty_', 'c')\
            + from_str
    query_str_0 = 'SELECT'\
            + ' group_concat(c, ", ") AS gc'\
            + ' FROM (' + inner_str + ')'
    cursor.execute(query_str_0, [myid])
    select_str = cursor.fetchall()[0][0]
    if select_str is None:
        select_str = 'COUNT(*)'
    #### get pivot table
    query_str_1 = 'SELECT auth_user.username'
    query_str_1 += (
        ', SUM(item_request.quantity) AS SUM'
        ', SUM(enabled_item_for_Z.price'
        ' * item_request.quantity) AS GT'
    )
    query_str_1 += ', ' + select_str
    query_str_1 += (
        ' FROM "item_request"'
        ' INNER JOIN "enabled_item_for_Z" ON '
        ' ("item_request"."enabled_item_for_Z_id"'
        ' = "enabled_item_for_Z"."id")'
        ' INNER JOIN "auth_user" ON'
        ' ("item_request"."user_id" = "auth_user"."id")'
        ' WHERE "enabled_item_for_Z"."Z_id" = %s'
        ' GROUP BY item_request.user_id'
    )
    cursor.execute(query_str_1, [myid])
    request_list = cursor.fetchall()
    #### get total row
    total_row = [sum(x) if i > 0 else 'Total' for i, x
                 in enumerate(zip(*request_list))]
    #### ...