(
[id] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[name] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[value] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
–创建表
INSERT INTO dbo.a
( id, name, value )
VALUES ( N’1′, — id – nchar(10)
N’张三’, — name – nchar(10)
N’1′ — value – nchar(10)
),
( N’2′, — id – nchar(10)
N’张三’, — name – nchar(10)
N’2′ — value – nchar(10)
),
( N’3′, — id – nchar(10)
N’李四 ‘, — name – nchar(10)
N’1′ — value – nchar(10)
),
( N’4′, — id – nchar(10)
N’李四’, — name – nchar(10)
N’2′ — value – nchar(10)
),
( N’5′, — id – nchar(10)
N’王五’, — name – nchar(10)
N’1′ — value – nchar(10)
),
( N’6′, — id – nchar(10)
N’王五’, — name – nchar(10)
N’2′ — value – nchar(10)
)
–插入例子数据
SELECT * FROM a
–查询表
SELECT rTRIM(a.value)+’,’ FROM a FOR XML PATH(”)
–把value列拼接起来，隔开
SELECT b.name,
(SELECT rTRIM(value)+’,’ FROM a WHERE name=b.name FOR XML PATH(”)) AS valuenew FROM dbo.a AS b GROUP BY name
–按照name分组,计算当前name的value值的拼接
SELECT name,LEFT(valuenew,LEN(valuenew)-1)as valuenew,LEN(LEFT(valuenew,LEN(valuenew)-1)) as valuenewlen,LEN(a.valuenew)AS 原长度 FROM (
SELECT b.name,
(SELECT rTRIM(value)+’,’ FROM a WHERE name=b.name FOR XML PATH(”)) AS valuenew FROM dbo.a AS b GROUP BY name ) a
–left方法保留字符串的长度减一的字符串
–或
SELECT name,STUFF(a.valuenew,LEN(a.valuenew),1,”) as valuenew ,LEN(STUFF(a.valuenew,LEN(a.valuenew),1,”)) as valuenewlen,LEN(a.valuenew)AS 原长度 FROM (
SELECT b.name,
(SELECT rTRIM(value)+’,’ FROM a WHERE name=b.name FOR XML PATH(”)) AS valuenew FROM dbo.a AS b GROUP BY name) a
–stuff方法把字符串最后一位开始,第一字符删除,插入空
–去除拼接的最后一个字符逗号
