前端之家收集整理的这篇文章主要介绍了
关于表中列的聚合的解决,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
所以,当我们在处理下列要求时,会比较麻烦:
有表tb,如下:
idvalue
1aa
1bb
2aaa
2bbb
2ccc
需要得到结果:
id
values
1aa,bb
2aaa,bbb,ccc
即,
group
by
id,求value的和(字符串相加)
CREATE
FUNCTION
dbo.f_str(@id
int
)
RETURNS
varchar
(8000)
AS
BEGIN
DECLARE
@r
varchar
(8000)
SET
@r=
''
SELECT
@r=@r+
','
+value
FROM
tb
WHERE
id=@id
RETURN
STUFF(@r,1,
''
)
END
GO
SELECt
id,
values
=dbo.f_str(id)
FROM
tb
GROUP
BY
id
DECLARE
@t
TABLE
(id
int
,value
varchar
(10))
INSERT
@t
SELECT
1,
'aa'
UNION
ALL
SELECT
1,
'bb'
UNION
ALL
SELECT
2,
'aaa'
UNION
ALL
SELECT
2,
'bbb'
UNION
ALL
SELECT
2,
'ccc'
SELECT
*
FROM
(
SELECT
DISTINCT
id
FROM
@t
)A
OUTER
APPLY(
SELECT
[
values
]=STUFF(
REPLACE
(
REPLACE
(
(
SELECT
value
FROM
@tN
WHERE
id=A.id
FOR
XMLAUTO
),
'<Nvalue="'
,
','
),
'"/>'
,
''
),
''
)
)N
/*
id
values
1aa,bb
2aaa,ccc
(2行受影响)
@H_206_
502@