实现以下表中Msg字段的拼接:ID Time Msg
1 2009-1-1 10:10:11:10 中华
1 2009-1-1 10:10:11:20 人民
1 2009-1-1 10:10:11:30 共和国
2 2009-2-2 10:10:11:10 t1
2 2009-2-2 10:10:11:20 t2
2 2009-2-2 10:10:11:30 t3
2 2009-2-2 10:10:11:40 t4
2 2009-2-2 10:10:11:10 t5
3 2009-3-3 10:10:11:10 st1
3 2009-3-3 10:10:11:10 st2
3 2009-3-3 10:10:11:10 st3
3 2009-3-3 10:10:11:10 st4
3 2009-3-3 10:10:11:10 st5
3 2009-3-3 10:10:11:10 st6
3 2009-3-3 10:10:11:10 st7
4 2009-4-3 10:10:11:10 test1实现的效果:将上表中ID号相同记录的Msg的内容拼接起来,并且显示由几个字段拼接的,Time字段,选第一个
拼接后的效果如下:
ID Time MsgMerge Count
1 2009-1-1 10:10:11:10 中华人民共和国 3
2 2009-2-2 10:10:11:10 t1t2t3t4t5 5
3 2009-3-3 10:10:11:10 st1st2st3st4st5st6st7 7
4 2009-4-3 10:10:11:10 test1 1
1 2009-1-1 10:10:11:10 中华
1 2009-1-1 10:10:11:20 人民
1 2009-1-1 10:10:11:30 共和国
2 2009-2-2 10:10:11:10 t1
2 2009-2-2 10:10:11:20 t2
2 2009-2-2 10:10:11:30 t3
2 2009-2-2 10:10:11:40 t4
2 2009-2-2 10:10:11:10 t5
3 2009-3-3 10:10:11:10 st1
3 2009-3-3 10:10:11:10 st2
3 2009-3-3 10:10:11:10 st3
3 2009-3-3 10:10:11:10 st4
3 2009-3-3 10:10:11:10 st5
3 2009-3-3 10:10:11:10 st6
3 2009-3-3 10:10:11:10 st7
4 2009-4-3 10:10:11:10 test1实现的效果:将上表中ID号相同记录的Msg的内容拼接起来,并且显示由几个字段拼接的,Time字段,选第一个
拼接后的效果如下:
ID Time MsgMerge Count
1 2009-1-1 10:10:11:10 中华人民共和国 3
2 2009-2-2 10:10:11:10 t1t2t3t4t5 5
3 2009-3-3 10:10:11:10 st1st2st3st4st5st6st7 7
4 2009-4-3 10:10:11:10 test1 1
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加) 1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb
http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx
create table tb (a varchar(10),b varchar(10))
insert tb
select '2009', 'AA' union all
select '2009', 'BB' union all
select '2008', 'CC' union all
select '2007', 'XX' union all
select '2009', 'HH' union all
select '2008', 'DD' union all
select '2007', 'SS' union all
select '2006', 'GG'
go
select * from tb
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(b as varchar)
FROM tb
WHERE a=@col1
RETURN(STUFF(@re,1,1,''))
END
GOselect a,dbo.f_str(a)[b] from tb group by a a b
---------- -------------------
2006 GG
2007 XX,SS
2008 CC,DD
2009 AA,BB,HH(所影响的行数为 4 行)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-07 15:12:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Time] datetime,[Msg] varchar(6))
insert [tb]
select 1,'2009-1-1 10:10:11:10','中华' union all
select 1,'2009-1-1 10:10:11:20','人民' union all
select 1,'2009-1-1 10:10:11:30','共和国' union all
select 2,'2009-2-2 10:10:11:10','t1' union all
select 2,'2009-2-2 10:10:11:20','t2' union all
select 2,'2009-2-2 10:10:11:30','t3' union all
select 2,'2009-2-2 10:10:11:40','t4' union all
select 2,'2009-2-2 10:10:11:10','t5' union all
select 3,'2009-3-3 10:10:11:10','st1' union all
select 3,'2009-3-3 10:10:11:10','st2' union all
select 3,'2009-3-3 10:10:11:10','st3' union all
select 3,'2009-3-3 10:10:11:10','st4' union all
select 3,'2009-3-3 10:10:11:10','st5' union all
select 3,'2009-3-3 10:10:11:10','st6' union all
select 3,'2009-3-3 10:10:11:10','st7' union all
select 4,'2009-4-3 10:10:11:10','test1'
--------------开始查询--------------------------
;with f as
(select
id,[Time], [Msg]=stuff((select ''+[Msg] from tb t where id=tb.id for xml path('')), 1, 0, '')
from
tb
group by
id,[Time])
select id,max(time) as time,max(msg) as msg from f group by id
----------------结果----------------------------
/*id time msg
----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2009-01-01 10:10:11.030 中华人民共和国
2 2009-02-02 10:10:11.040 t1t2t3t4t5
3 2009-03-03 10:10:11.010 st1st2st3st4st5st6st7
4 2009-04-03 10:10:11.010 test1(4 行受影响)
*/