如何将列值作为列名得到查询结果

Question

表结构可以抽象为如下形式，其中id不唯一，可重复，与city对应
id   city     user_type    num
1    beijing  A            20
1    beijing  C            15
2    shanghai A            30
2    shanghai B            16
2    shanghai C            18
想得到的结果如下：
city     A     B     C
beijing  20          15
shanghai 30    16    18
total    50    16    33
数据库：oracle 8i
开发语言：C++
上面问题可以分为两种情况：1.user_type种类固定只有3种，2.user_type种类可能有多种

Answer 1

SQL> select * from t;                                     ID CITY       USER_TYPE                                     NUM
--------------------------------------- ---------- --------- ---------------------------------------
                                      1 Beijing    A                                              20
                                      1 Beijing    C                                              15
                                      2 Shanghai   A                                              30
                                      2 Shanghai   B                                              16
                                      2 Shanghai   C                                              18SQL> edit
SQL> select decode(grouping(city),1,'Total',city),
  2  sum(decode(user_type,'A',c,0)) as "A",sum(decode(user_type,'B',c,0)) as "B",sum(decode(user_type,'C',c,0)) as "C"
  3  from ( select city,user_type,sum(num) as c from t group by city,user_type) group by rollup(city)
  4  /DECODE(GROUPING(CITY),1,'TOTAL          A          B          C
------------------------------ ---------- ---------- ----------
Beijing                                20          0         15
Shanghai                               30         16         18
Total                                  50         16         33

Answer 2

SQL> select * from t;                                     ID CITY       USER_TYPE                                     NUM
--------------------------------------- ---------- --------- ---------------------------------------
                                      1 Beijing    A                                              20
                                      1 Beijing    C                                              15
                                      2 Shanghai   A                                              30
                                      2 Shanghai   B                                              16
                                      2 Shanghai   C                                              18SQL> edit
SQL> edit
SQL> 
SQL> select decode(grouping(city),1,'Total',city),
  2  sum(decode(user_type,'A',c,0)) as "A",sum(decode(user_type,'B',c,0)) as "B",sum(decode(user_type,'C',c,0)) as "C"
  3  from ( select city,user_type,sum(num) as c from t group by city,user_type) group by rollup(city)
  4  /DECODE(GROUPING(CITY),1,'TOTAL          A          B          C
------------------------------ ---------- ---------- ----------
Beijing                                20          0         15
Shanghai                               30         16         18
Total                                  50         16         33

Answer 3

算了,这个插入SQL代码玩不好,将就看

Answer 4

高手啊，多谢
第二种是不是就没有办法了：）2.user_type种类可能有多种
不过第一种已经可以解决很大问题了