一道SQL题目，来看看吧

原表:

id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表:

id pro1 pro2
1 M F
2 N G
3 B A
写出查询语句
解决方案

SQL> select * from ( select id,proname pro1,lead(proname,1,null) over(partition by id order by id) as pro2 from t )where pro2 is not null;

        ID PRO1                           PRO2
---------- ------------------------------ ------------------------------
         1 M                              F
         2 G                              N
         3 B                              A

SQL> select * from t;

        ID      PROID PRONAME
---------- ---------- ------------------------------
         1          1 M
         1          2 F
         2          2 G
         2          1 N
         3          1 B
         3          2 A

6 rows selected.

select id,wm_concat(decode(proid,1,proname)) pro1,
  wm_concat(decode(proid,2,proname)) pro2
from t
group by id;

用的是MySQL：
select t1.id,t1.proname as name1,t2.proname as name2
from t t1 , t t2 
where t1.proname != t2.proname
and t1.id=t2.id
GROUP BY t1.id

oracle：
select  t1.id ,t1.proname,t2.proname
from test1 t1,test1 t2
where t1.id = t2.id and t1.proid <>t2.proid and t1.proid<>2;

这个wm_concat是oracle 10g中才有额

select两个result出来再用id join起来，函数都不需要

select 
b11.id,b11.proname,a22.proname

from
--  视图1,得出最小值
(
select t1.id, b1.pro1, t1.proname
from t1,( select id ,min(proid) as proid  from t1 group id ) as b1
where t1.id = b1.id
) as b11,

-- 视图2，得出最大值
(
select t1.id, a1.pro1, t1.proname
from t1,( select id ,max(proid) as proid  from t1 group id ) as a1
where t1.id = a1.id
) as a22

--
where b11.id = a22.id

时间关系，写了个有缺陷的东西，对于3个以上的排序就无能为力了，但应该适用任何数据库

同事给了一个，比我的好：
select id,max(case proid when 1 then proname else 'A' end) as pro1,max(case proid when 2 then proname else 'A' end) as pro2 from xxx group by id

楼上的正解 行列转换 我也这么弄