关于表中列的聚合的解决
前端之家收集整理的这篇文章主要介绍了
关于表中列的聚合的解决，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
所以，当我们在处理下列要求时，会比较麻烦：
有表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
@H_
403_416@
FOR
XMLAUTO
),
'<Nvalue="'
,
','
),
'"/>'
,
''
),
''
)
)N
/*
id
values
1aa,bb
2aaa,ccc
(2行受影响)
@H_136_
502@
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
DECLARE
@t
TABLE
(col1
varchar
(10),col2
varchar
(100))
DECLARE
tb
CURSOR
LOCAL
FOR
SELECT
col1,col2
FROM
tb
ORDER
BY
col1,col2
DECLARE
@col1_old
varchar
(10),@col1
varchar
(10),@col2
int
,@s
varchar
(100)
OPEN
tb
FETCH
tb
INTO
@col1,@col2
SELECT
@col1_old=@col1,@s=
''
WHILE@@FETCH_STATUS=0
BEGIN
IF@col1=@col1_old
SELECT
@s=@s+
','
+
CAST
(@col2
as
varchar
)
ELSE
BEGIN
INSERT
@t
VALUES
(@col1_old,STUFF(@s,
''
))
SELECT
@s=
','
+
CAST
(@col2
as
varchar
),@col1_old=@col1
END
FETCH
tb
INTO
@col1,@col2
END
INSERT
@t
VALUES
(@col1_old,
''
))
CLOSE
tb
DEALLOCATE
tb
SELECT
*
FROM
@t
DROP
TABLE
tb
/*
col1col2
a1,2
b1,2,3
GO
/*==============================================*/
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
GO
CREATE
FUNCTION
dbo.f_str(@col1
varchar
(10))
RETURNS
varchar
(100)
AS
BEGIN
DECLARE
@re
varchar
(100)
SET
@re=
''
SELECT
@re=@re+
','
+
CAST
(col2
as
varchar
)
FROM
tb
WHERE
col1=@col1
RETURN
(STUFF(@re,
''
))
END
GO
SELECT
col1,col2=dbo.f_str(col1)
FROM
tb
GROUP
BY
col1
DROP
TABLE
tb
DROP
FUNCTION
f_str
/*
col1col2
a1,2
b1,3
GO
/*==============================================*/
CREATE
TABLE
tb(col1
varchar
(10),col2
int
)
INSERT
tb
SELECT
'a'
,1
UNION
ALL
SELECT
'a'
,2
UNION
ALL
SELECT
'b'
,1
UNION
ALL
SELECT
'b'
,2
UNION
ALL
SELECT
'b'
,3
SELECT
col1,col2=
CAST
(col2
as
varchar
(100))
INTO
#t
FROM
tb
ORDER
BY
col1,col2
DECLARE
@col1
varchar
(10),@col2
varchar
(100)
UPDATE
#t
SET
@col2=
CASE
WHEN
@col1=col1
THEN
@col2+
','
+col2
ELSE
col2
END
,
@col1=col1,
col2=@col2
SELECT
*
FROM
#t
/*
@H_876_1
404@
col1col2
