Split rows data to columns

Question

Hi,

I have a table, that has a simple structure:

user_id, month, balance, balance_type

I want to display selected balance types per month for each user that is:

user_id, month, balance_1, balance_2, balance_3, balance_...

So from data:

ROW user_id month balance balance_type    
1   5667    09   20         2068
2   5667    08   23         2068
3   5667    07   21         2068
4   5667    06   19         2068
5   5667    10   22         2068
6   5667    09   20         2069
7   5667    08   23         2069
8   5667    06   19         2069
9   5667    07   21         2069
10  5667    10   22         2069
11  5667    09   4199       2114
12  5667    06   4329       2114
13  5667    08   4365       2114    
14  5667    10   4172,88    2114    
15  5667    07   4000       2114    
16  5667    10   572,1      6062    
17  5667    08   598,44     6062    
18  5667    07   548,4      6062    
19  5667    06   593,51     6062    
20  5667    09   575,69     6062

I would get for example for month 09:

user_id, month, balance_1, balance_2, balance_3, balance_4    
5667    09 20 20 4199 575,69

What is the best solution for this in SQL or/and PL/SQL?

Answer 1

If this is a one time requirement, I can suggest a hack. Use multiple self joins on user_id and month (you will need as many joins as there are balance types).

select a.user_id, a.month, a.balance balance_1, b.balance balance_2, c.balance balance_3...
from mytable a, mytable b, mytable c....
where 
a.month = 9 and
a.balance_type=1 and
a.user_id = b.user_id and
a.month = b.month and
b.balance_type=2 and
b.user_id = c.user_id and 
c.user_id = d.user_id and
c.balance_type=3....

This solution may not be the best but works like a charm as a one time hack.

Answer 2

If you have a fixed number of balance types and (user_id, month, balance_type) are unique tuples, then you can just do a inline subquery for each balance type. For example:

select user_id, month, 
(select balance from balance_table bt where bt.user_id = user_id and bt.month = month and bt.balance_type = '1'),
(select balance from balance_table bt where bt.user_id = user_id and bt.month = month and bt.balance_type = '2') .....
from balance_table

If its possible to have multiple rows having the same balance_type, month and user_id then you would want to use the pivot command to add up balances for each group, which exists both in SQL Server and Oracle (11g only). Say for example you have balance_types 1,2,3,4

select * from balance_table pivot (select sum(balance) for balance_type IN (1,2,3,4))

If you don't know how many balance_types you have beforehand, then I think dynamically generated sql is the only way to go, in which case you should use for example Oracle's DBMS_SQL PL/SQL package.

Answer 3

If you are using 11G see the PIVOT clause: http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/analysis.htm#DWHSG0209

If are using 9i or 10g see this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:419593546543 and this http://asktom.oracle.com/pls/ask/search?p_string=pivot.