【SQL精彩语句】合并列值

  1  -- 合并列值
  2  -- 原著：邹建
  3  -- 改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳
  4 
  5  -- 表结构，数据如下：
  6  /*
  7  id    value 
  8  ----- ------ 
  9  1    aa 
 10  1    bb 
 11  2    aaa 
 12  2    bbb 
 13  2    ccc 
 14  */
 15  -- 需要得到结果：
 16  /*
 17  id    values 
 18  ------ ----------- 
 19  1      aa,bb 
 20  2      aaa,bbb,ccc 
 21  即：group by id, 求value 的和（字符串相加）
 22  */
 23  -- 1. 旧的解决方法(在sql server 2000中只能用函数解决。) 
 24  -- 1. 创建处理函数
 25  create  table tb(id  int, value  varchar( 10)) 
 26  insert  into tb  values( 1,  ' aa ') 
 27  insert  into tb  values( 1,  ' bb ') 
 28  insert  into tb  values( 2,  ' aaa ') 
 29  insert  into tb  values( 2,  ' bbb ') 
 30  insert  into tb  values( 2,  ' ccc ') 
 31  go 
 32 
 33  create  function dbo.f_str( @id  int) 
 34  returns  varchar( 8000) 
 35  as 
 36  begin 
 37      declare  @r  varchar( 8000) 
 38      set  @r  =  '' 
 39      select  @r  =  @r  +  ' , '  + value  from tb  where id = @id 
 40      return  stuff( @r,  1,  1,  '') 
 41  end 
 42  go 
 43 
 44  --  调用函数
 45  SELECt id, value  = dbo.f_str(id)  FROM tb  GROUP  BY id 
 46 
 47  drop  table tb 
 48  drop  function dbo.f_str 
 49 
 50  /*  
 51  id          value      
 52  ----------- ----------- 
 53  1          aa,bb 
 54  2          aaa,bbb,ccc 
 55  （所影响的行数为2 行）
 56  */ 
 57 
 58  -- 2、另外一种函数. 
 59  create  table tb(id  int, value  varchar( 10)) 
 60  insert  into tb  values( 1,  ' aa ') 
 61  insert  into tb  values( 1,  ' bb ') 
 62  insert  into tb  values( 2,  ' aaa ') 
 63  insert  into tb  values( 2,  ' bbb ') 
 64  insert  into tb  values( 2,  ' ccc ') 
 65  go 
 66 
 67  -- 创建一个合并的函数
 68  create  function f_hb( @id  int) 
 69  returns  varchar( 8000) 
 70  as 
 71  begin 
 72    declare  @str  varchar( 8000) 
 73    set  @str  =  '' 
 74    select  @str  =  @str  +  ' , '  +  cast(value  as  varchar)  from tb  where id  =  @id 
 75    set  @str  =  right( @str ,  len( @str)  -  1) 
 76    return( @str) 
 77  End 
 78  go 
 79 
 80  -- 调用自定义函数得到结果：
 81  select  distinct id ,dbo.f_hb(id)  as value  from tb 
 82 
 83  drop  table tb 
 84  drop  function dbo.f_hb 
 85 
 86  /*  
 87  id          value      
 88  ----------- ----------- 
 89  1          aa,bb 
 90  2          aaa,bbb,ccc 
 91  （所影响的行数为2 行）
 92  */ 
 93 
 94  -- 2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 
 95  create  table tb(id  int, value  varchar( 10)) 
 96  insert  into tb  values( 1,  ' aa ') 
 97  insert  into tb  values( 1,  ' bb ') 
 98  insert  into tb  values( 2,  ' aaa ') 
 99  insert  into tb  values( 2,  ' bbb ') 
100  insert  into tb  values( 2,  ' ccc ') 
101  go 
102  --  查询处理
103  select  *  from( select  distinct id  from tb)a  outer apply( 
104          select  [ values ] =  stuff( replace( replace( 
105             ( 
106                  select value  from tb n 
107                  where id  = a.id 
108                  for xml auto 
109             ),  '  <N value=" ',  ' , '),  ' "/> ',  ''),  1,  1,  '') 
110 )N 
111  drop  table tb
112 
113  /*  
114  id          values 
115  ----------- ----------- 
116  1          aa,bb 
117  2          aaa,bbb,ccc 
118 
119  (2 行受影响) 
120  */ 
121 
122  -- SQL2005中的方法
123  create  table tb(id  int, value  varchar( 10)) 
124  insert  into tb  values( 1,  ' aa ') 
125  insert  into tb  values( 1,  ' bb ') 
126  insert  into tb  values( 2,  ' aaa ') 
127  insert  into tb  values( 2,  ' bbb ') 
128  insert  into tb  values( 2,  ' ccc ') 
129  go 
130 
131  select id,  [ values ] = stuff(( select  ' , ' + [ value ]  from tb t  where id =tb.id  for xml path( '')),  1,  1,  '') 
132  from tb 
133  group  by id 
134 
135  /*  
136  id          values 
137  ----------- -------------------- 
138  1          aa,bb 
139  2          aaa,bbb,ccc 
140 
141  (2 row(s) affected) 
142 
143  */ 
144 
145  drop  table tb

转载于:https://www.cnblogs.com/temptation/archive/2012/01/30/2332344.html