select column1,(select ','+column2 from table1 where column1=a.column1 for XML path('')) as column2 from table1 as a group by column1
补充 数据库 MS SQL Server 2005
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA CREATE TABLE TA (T1 VARCHAR(10),T2 VARCHAR(50)) INSERT INTO TA SELECT 'A','1' UNION ALL SELECT 'A','2' UNION ALL SELECT 'B','2' UNION ALL SELECT 'C','1' UNION ALL SELECT 'C','3' UNION ALL SELECT 'C','4' --查询 SELECT T1,(SELECT ','+T2 FROM TA AS A WHERE A.T1=TA.T1 FOR XML PATH('')) AS T2 FROM TA GROUP BY T1 --结果 T1 T2 --------------- A ,1,2 B ,2 C ,1,3,4(3 行受影响)
不会表述? 这个叫列值合并select column1, column2=(select ','+column2 from tb1 where column1=a.column1 for XML path ('')) from tb agroup by column1
select column1, column2=stuff(select ','+column2 from tb1 where column1=a.column1 for XML path (''),1,1,'') from tb a group by column1这个可以去掉table2中column2列值的第一个逗号
from table1 as a
group by column1
数据库 MS SQL Server 2005
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA (T1 VARCHAR(10),T2 VARCHAR(50))
INSERT INTO TA
SELECT 'A','1' UNION ALL
SELECT 'A','2' UNION ALL
SELECT 'B','2' UNION ALL
SELECT 'C','1' UNION ALL
SELECT 'C','3' UNION ALL
SELECT 'C','4'
--查询
SELECT T1,(SELECT ','+T2 FROM TA AS A WHERE A.T1=TA.T1 FOR XML PATH('')) AS T2
FROM TA
GROUP BY T1
--结果
T1 T2
---------------
A ,1,2
B ,2
C ,1,3,4(3 行受影响)
column2=(select ','+column2 from tb1 where column1=a.column1 for XML path (''))
from tb agroup by column1
column2=stuff(select ','+column2 from tb1 where column1=a.column1 for XML path (''),1,1,'')
from tb a
group by column1这个可以去掉table2中column2列值的第一个逗号