基础基础基础解决方案
基础基础基础
CREATE TABLE T_T (NAME VARCHAR(100), val VARCHAR(100))
INSERT INTO T_T SELECT '小明', '100'
UNION ALL SELECT '小明', '100'
UNION ALL SELECT '小明','200'
UNION ALL SELECT '小明','300'
SELECT * FROM T_T
想得到
NAME val
小明 100,200,300 【排除掉相同的数据】
------解决方案--------------------
------解决方案--------------------
CREATE TABLE T_T (NAME VARCHAR(100), val VARCHAR(100))
INSERT INTO T_T SELECT '小明', '100'
UNION ALL SELECT '小明', '100'
UNION ALL SELECT '小明','200'
UNION ALL SELECT '小明','300'
SELECT * FROM T_T
想得到
NAME val
小明 100,200,300 【排除掉相同的数据】
------解决方案--------------------
CREATE TABLE T_T (NAME VARCHAR(100), val VARCHAR(100))
INSERT INTO T_T SELECT '小明', '100'
UNION ALL SELECT '小明', '100'
UNION ALL SELECT '小明','200'
UNION ALL SELECT '小明','300'
select a.NAME,
stuff( (select DISTINCT ','+val from T_T b
where b.NAME=a.NAME
for xml path('')),1,1,'') 'val'
from T_T a
group by a.NAME
/*
NAME val
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
小明 100,200,300
*/
------解决方案--------------------
CREATE TABLE T_T (NAME VARCHAR(100), val VARCHAR(100))
INSERT INTO T_T SELECT '小明', '100'
UNION ALL SELECT '小明', '100'
UNION ALL SELECT '小明','200'
UNION ALL SELECT '小明','300'
--SELECT * FROM T_T
select a.NAME,stuff( (select ','+val from (select distinct * from T_T) b where b.NAME=a.NAME for xml path('')),1,1,'') 'val' from T_T a group by a.NAME
drop table T_T
/*NAME val
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
小明 100,200,300
*/