同一个表的多条数据合并成一条，挺难，请教高手!!

[httpftp]

规则是这样的：
表T：
ID SCHOOL_ID GRADE SUBJECT_ID STUDENT_ID
1 s1 1 a 001
2 s1 1 a 002
3 s1 1 b 111
4 s1 1 c 001
5 s1 1 c 104
6 s1 1 d 021
7 s1 2 a 101
8 s1 2 b 101

根据SCHOOL_ID和GRADE分组，取前三个SUBJECT_ID，并且得到一个FLAG，如果多于三个设‘1’，不足设‘0’；同时取每个（SCHOOL_ID GRADE SUBJECT_ID）下面STUDENT_ID的个数。
得到结果如下：
SCHOOL_ID GRADE SUBJECT_1 SUBJECT_2 SUBJECT_3 FALG STUDENT_SUM_1 STUDENT_SUM_2 STUDENT_SUM_3
s1 1 a b c 1 2 1 2
s1 2 a b 0 1 1

只用SQL文。
这个问题我思考了一下午，不得解，求高人们指点。

[httpftp]

哇哈哈，奋斗了又一下午，终于把这个SQL写出来了！

SELECT T2.SCHOOL_ID,
T2.GRADE,
COUNT(T2.SUBJECT_ID) SUBJECT_COUNT,
MAX(T2.SUBJECT_CD1) SUBJECT_CD1,
MAX(T2.SUBJECT_CD2) SUBJECT_CD2,
MAX(T2.SUBJECT_CD3) SUBJECT_CD3,
MAX(T2.PEOPLE_COUNT1) PEOPLE_COUNT1,
MAX(T2.PEOPLE_COUNT2) PEOPLE_COUNT2,
MAX(T2.PEOPLE_COUNT3) PEOPLE_COUNT3
FROM
(
SELECT T1.SCHOOL_ID,T1.GRADE,T1.SUBJECT_ID,
CASE T1.ROW WHEN 1 THEN T1.SUBJECT_CD ELSE NULL END SUBJECT_CD1,
CASE T1.ROW WHEN 2 THEN T1.SUBJECT_CD ELSE NULL END SUBJECT_CD2,
CASE T1.ROW WHEN 3 THEN T1.SUBJECT_CD ELSE NULL END SUBJECT_CD3,
CASE T1.ROW WHEN 1 THEN T1.PEOPLE_COUNT ELSE NULL END PEOPLE_COUNT1,
CASE T1.ROW WHEN 2 THEN T1.PEOPLE_COUNT ELSE NULL END PEOPLE_COUNT2,
CASE T1.ROW WHEN 3 THEN T1.PEOPLE_COUNT ELSE NULL END PEOPLE_COUNT3,
FROM
(
SELECT FROWNUMBER()OVER(PARTITION BY SCHOOL_ID,GRADE,SUBJECT_ID) AS ROW,
SCHOOL_ID,
GRADE,
SUBJECT_ID,
COUNT(STUDENT_ID) PEOPLE_COUNT
FROM T GROUP BY SCHOOL_ID,GRADE,SUBJECT_ID
) T1
) T2

我不能保证这是最好的写法，也许还有优化解，希望朋友们拿出来讨论。
如果我看到比较好的解决方案，一样会给分的。

[httpftp]

追加问题：还是这个表。上面是有固定列数要求的行转列，用CASE语句实现了。如果没有固定列数要求，应该怎么实现啊？

现在纯属学习，征集各种解法。

谢谢！

[httpftp]

yangxiao_jiang 东北大胖
的BLOG我看了，里面的解法得到的是只有一列的表，你把所有的字段都连成了一个字符串。
能不能优化一下，得到这样的结果啊：
SCHOOL_ID GRADE SUBJECT_1 SUBJECT_2 SUBJECT_3 FALG STUDENT_SUM_1 STUDENT_SUM_2 STUDENT_SUM_3
s1 1 a b c 1 2 1 2
s1 2 a b 0 1 1

[xiequanqin]

一个Group + 行转列

[yangxiao_jiang]

http://www.cublog.cn/u/739/showart.php?id=278401