有2条记录: no state
1 100w1 1
2 100w2 3
3 101w1 2
4 101w2 6现在要合成按NO字段合成一条记录, state字段连接起来, 如:"1,3"1 100w 1,3
2 101w 2.6如 SELECT SUBSTR(No,1,LENGTH(No)-1) FROM 表 GROUP BY SUBSTR(No,1,LENGTH(No)-1)1 100w
2 101w但是字段state应该怎么连接,请教?
1 100w1 1
2 100w2 3
3 101w1 2
4 101w2 6现在要合成按NO字段合成一条记录, state字段连接起来, 如:"1,3"1 100w 1,3
2 101w 2.6如 SELECT SUBSTR(No,1,LENGTH(No)-1) FROM 表 GROUP BY SUBSTR(No,1,LENGTH(No)-1)1 100w
2 101w但是字段state应该怎么连接,请教?
SELECT '100w1'no,1 state FROM dual
UNION ALL
SELECT '100w2',3 FROM dual
UNION ALL
SELECT '101w1',2 FROM dual
UNION ALL
SELECT '101w2',6 FROM dual
)
SELECT SUBSTR(No,1,LENGTH(No)-1)no,wm_concat(state)state FROM t GROUP BY SUBSTR(No,1,LENGTH(No)-1)