SQL合并数据

> 把记录集中的多条相同的记录合并成一条记录SQL code
> /*
> 带符号合并行列转换(爱新觉罗.毓华 2007-11-19于海南三亚)
>
> 有表tb，其数据如下：
>  a b
>  1 1
>  1 2
>  1 3
>  2 1
>  2 2
>  3 1
> 如何转换成如下结果:
>  a b
>  1 1,2,3
>  2 1,2
>  3 1
> */
>
> create table tb
> (
>   a int,
>   b int
> )
> insert into tb(a,b) values(1,1)
> insert into tb(a,b) values(1,2)
> insert into tb(a,b) values(1,3)
> insert into tb(a,b) values(2,1)
> insert into tb(a,b) values(2,2)
> insert into tb(a,b) values(3,1)
> go
>
> --创建一个合并的函数
> create function f_hb(@a int)
> returns varchar(8000)
> as
> begin
>  declare @str varchar(8000)
>  set @str = ''
>  select @str = @str + ',' + cast(b as varchar) from tb where a = @a
>  set @str = right(@str , len(@str) - 1)
>  return(@str)
> End
> go
>
> --调用自定义函数得到结果：
> select distinct a ,dbo.f_hb(a) as b from tb
>
> drop table tb
> drop function f_hb
>
> /*
> 结果
> a           b    
> ----------- ------
> 1           1,2,3
> 2           1,2
> 3           1
>
> （所影响的行数为 3 行）
> */
>
> ----------------------------------------------------
> /*
> 多个前列的合并
> 数据的原始状态如下：
> ID  PR   CON  OP    SC
> 001 p    c    差    6
> 001 p    c    好    2
> 001 p    c    一般  4
> 002 w    e    差    8
> 002 w    e    好    7
> 002 w    e    一般  1
> 用SQL语句实现，变成如下的数据
> ID  PR   CON  OPS
> 001 p    c    差(6),好(2),一般(4)
> 002 w    e    差(8),好(7),一般(1)
> */
>
> create table tb
> (
>  id  varchar(10),
>  pr  varchar(10),
>  con varchar(10),
>  op  varchar(10),
>  sc  int
> )
>
> insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '差',    6)
> insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '好',    2)
> insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '一般',  4)
> insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '差',    8)
> insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '好',    7)
> insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '一般',  1)
> go
>
> --创建一个合并的函数
> create function f_hb(@id varchar(10) , @pr varchar(10) , @con varchar(10))
> returns varchar(8000)
> as
> begin
>  declare @str varchar(8000)
>  set @str = ''
>  select @str = @str + ',' + cast(OP as varchar) + '('
>                           + cast(sc as varchar) + ')'
>  from tb where id = @id and @pr = pr and @con = con
>  set @str = right(@str , len(@str) - 1)
>  return(@str)
> End
> go
>
> --调用自定义函数得到结果：
> select distinct id , pr , con , dbo.f_hb(id,pr,con) as ops from tb
>
> drop table tb
> drop function f_hb
>
> /*
> 结果
> id         pr         con        ops              
> ---------- ---------- ---------- ------------------
> 001        p          c          差(6),好(2),一般(4)
> 002        w          e          差(8),好(7),一般(1)
>
> （所影响的行数为 2 行）
> */
>
> ----------------------------------------------------
> /*如何将一列中所有的值一行显示
> 数据源
>  a
>  b
>  c
>  d
>  e
> 结果
> a,b,c,d,e
> */
>
> create table tb(col varchar(20))
> insert tb values ('a')
> insert tb values ('b')
> insert tb values ('c')
> insert tb values ('d')
> insert tb values ('e')
> go
>
> --方法一
> declare @sql varchar(1000)
> set @sql = ''
> select @sql = @sql + t.col + ',' from (select col from tb) as t
> set @sql='select result = ''' + @sql + ''''
> exec(@sql)
> /*
> result    
> ----------
> a,b,c,d,e,
> */
>
> --方法二
> declare @output varchar(8000)
> select @output = coalesce(@output + ',' , '') + col from tb
> print @output
> /*
> a,b,c,d,e
> */
>
> drop table tb
>
> --------------------------------------------------------------------合并列值
> 原著：邹建
> 改编：爱新觉罗.毓华  2007-12-16  广东深圳
>
> 表结构，数据如下：
> 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中只能用函数解决。)
> --1. 创建处理函数
> 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
>
> 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, 1, '')
> END
> GO
>
> -- 调用函数
> SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
>
> drop table tb
> drop function dbo.f_str
>
> /*
> id          value     
> ----------- -----------
> 1           aa,bb
> 2           aaa,bbb,ccc
> （所影响的行数为 2 行）
> */
>
> --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
>
> --创建一个合并的函数
> create function f_hb(@id int)
> returns varchar(8000)
> as
> begin
>  declare @str varchar(8000)
>  set @str = ''
>  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
>  set @str = right(@str , len(@str) - 1)
>  return(@str)
> End
> go
>
> --调用自定义函数得到结果：
> select distinct id ,dbo.f_hb(id) as value from tb
>
> drop table tb
> drop function dbo.f_hb
>
> /*
> 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 行受影响)
> */
>
>