sql多行合并

 例一

 

SELECT
    qqo.questionID '题目id',
    qqo.quesOption '选项'
FROM   qz_question_option qqo,
       qz_question qq
WHERE qqo.questionID = qq.questionID

上面多行记录合并为如下形式：（sqlserver可以用，其他未试）

SELECT
    qq.questionID '题目id',
    (SELECT
       qqo.quesOption + '  |  '
    FROM qz_question_option qqo
    WHERE qqo.questionID = qq.questionID AND qqo.valid = 1
    FOR xml PATH (''))
    AS '选项'
FROM qz_question qq

 例二

 

 同一个组的多个组长合并：（Oracle可用）

  SELECT sb.bch_cde, sb.bch_desc, sb.bch_sts, wm_concat(sagu.user_name)
    FROM S_BCH sb
    LEFT JOIN S_APPOVE_GROUP_USER sagu
      ON sb.bch_cde = sagu.group_id
     AND sagu.captain_flag = 'Y'
   WHERE bch_flag = '01'
     and BCH_SUP_CDE = '00020100'
     and BCH_STS = 'A'
   group by sb.bch_cde, sb.bch_desc, sb.bch_sts;