我有两张桌子,
表1有以下字段:
u_id id no
12 51 1
21 51 2
31 51 3
41 51 4
51 51 5
61 51 6
72 51 7
81 51 8
91 51 9
92 51 10表2有以下字段:
id one two three four five six seven eight nine ten
51 12 21 31 41 51 61 72 81 91 92我要查一下号码。和表1中的id,并将相应的u_id插入表2。
就像。如果id = 51,no是1,则必须将u-id值插入表2中的列1中,
和id = 51和no =2,然后插入第二栏,以此类推。请帮帮忙。我在用甲骨文。
发布于 2013-06-12 05:34:05
id必须是唯一的
select id,
sum(u_id*(if no = 1 then 1 else 0 endif)) as one,
sum(u_id*(if no = 2 then 1 else 0 endif)) as two,
sum(u_id*(if no = 3 then 1 else 0 endif)) as three,
sum(u_id*(if no = 4 then 1 else 0 endif)) as four,
sum(u_id*(if no = 5 then 1 else 0 endif)) as five,
sum(u_id*(if no = 6 then 1 else 0 endif)) as six,
sum(u_id*(if no = 7 then 1 else 0 endif)) as seven,
sum(u_id*(if no = 8 then 1 else 0 endif)) as eight,
sum(u_id*(if no = 9 then 1 else 0 endif)) as nine,
sum(u_id*(if no = 10 then 1 else 0 endif)) as ten
from table_1 group by id;发布于 2013-06-12 05:45:34
如果您想要创建一个新的表,或者只需要从数据库中返回这组数据集,您将需要数据透视表来完成此操作。
select * from table 1
pivot (max (u_id) for id in ([1],[2],[3],[4],[5],[6],[7],[8],[9])[10]) as table 2发布于 2013-06-12 05:10:37
我不认为一个查询就能做到这一点。您必须为此编写一个PLSQL块。
只是一个理论上的解释。
https://stackoverflow.com/questions/17057943
复制相似问题