将表中子字段的值按主字段分组后以空格连接作为一个字段

来源：互联网发布：网络手机卡资费对比编辑：程序博客网时间：2024/05/17 04:16

Oracle 11以上バージョン

SELECT

FIELD_zhu

,LISTAGG(FIELD_zi,' ') WITHIN GROUP (ORDER BY FIELD_zi) AS FIELD3

FROM TEST

GROUP BY FIELD_zhu;

Oracle11g 以前

SELECT   m1060.fvhmnm,
                    m1060.fvkikaku,
                    m1060.fvzkkrcd,
                    m1060.fvshcd,
                    m1060a.jancd,
                    m1060.fcdkgkkbn
FROM       M1060 m1060,
                   (
                   SELECT   fvzkkrcd,
                   (   MAX(DECODE(line_no,1,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,2,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,3,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,4,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,5,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,6,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,7,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,8,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,9,fvshcd)) || ' ' ||
                           MAX(DECODE(line_no,10,fvshcd))) jancd
                      FROM (
                                                   SELECT   ROW_NUMBER() OVER(ORDER BY fnpkgsortno) row_no,
                                                                   ROW_NUMBER() OVER(PARTITION BY fvzkkrcd ORDER BY fnpkgsortno) line_no,
                                                                   fvzkkrcd,
                                                                   fvshcd
                                                   FROM       M1060
                                                   ORDER BY fvshcd
                                                   )
                                       WHERE       line_no <= 10
                                       GROUP BY fvzkkrcd
                                   ) m1060a
WHERE m1060.fvshcd = m1060a.fvzkkrcd
ORDER BY m1060.fvzkkrcd,m1060a.jancd

0 0