name type
--------------------
test1 t1
test1 t2
test1 t3
.
. 中间有N个
.
test1 tn转换为
name type
--------------------
test1 t1,t2,t3,...,tn
--------------------
test1 t1
test1 t2
test1 t3
.
. 中间有N个
.
test1 tn转换为
name type
--------------------
test1 t1,t2,t3,...,tn
IF OBJECT_ID(N'Test') IS NOT NULL
DROP TABLE Test
GO
CREATE TABLE Test(name VARCHAR(10),TYPE VARCHAR(10))
INSERT INTO Test SELECT 'test1','t1'
UNION ALL SELECT 'test1','t2'
UNION ALL SELECT 'test1','t3'
--------------------------------------------------------------------------- 查询-----------------------------------------------
SELECT name, STUFF((SELECT ','+TYPE FROM Test b WHERE a.name=b.NAME FOR XML PATH('')),1,1,'') AS TYPE FROM Test a GROUP BY name---------------------------------------------------------------------------结果-----------------------------------------------
/*name TYPE
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test1 t1,t2,t3(1 行受影响)
*/