Group character merge SQL statement merge strings by a field (simple merge), SQL statement

Group character merge SQL statement merge strings by a field (simple merge), SQL statement

Title: Merge strings by a field (simple merge)

Description: merges the following data into the value field by id.
Id value
-----------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
Expected results:
Id value
-----------------
1 aa, bb
2 aaa, bbb, ccc
That is: group by id, sum of values (string addition)

1. sql2000 can only be solved using custom functions

Create table tb (id int, value varchar (10) insert into tb values (1, 'A') insert into tb values (1, 'bb ') insert into tb values (2, 'aaa') insert into tb values (2, 'bbb ') insert into tb values (2, 'ccc') gocreate function dbo. f_str (@ id int) returns varchar (100) asbegin declare @ str varchar (1000) set @ str = ''select @ str = @ str + ''+ cast (value as varchar) from tb where id = @ id set @ str = right (@ str, len (@ str)-1) return @ strendgo -- call the select id, value = dbo. f_str (id) from tb group by iddrop function dbo. f_strdrop table tb

2. Methods in sql2005

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')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tb group by iddrop table tb

3. Use a cursor to merge data

Create table tb (id int, value varchar (10) insert into tb values (1, 'A') insert into tb values (1, 'bb ') insert into tb values (2, 'aaa') insert into tb values (2, 'bbb ') insert into tb values (2, 'ccc ') godeclare @ t table (id int, value varchar (100) -- Define the result set table variable -- Define the cursor and merge it. declare my_cursor cursor local forselect id, value from tbdeclare @ id_old int, @ id int, @ value varchar (10), @ s varchar (100) open my_cursor fetch my_cursor into @ id, @ valueselect @ id_old = @ id, @ s = ''while @ FETCH_STATUS = 0 begin if @ id = @ id_old select @ s = @ s + ',' + cast (@ value as varchar) else begin insert @ t values (@ id_old, stuff (@ s, 1, 1, '') select @ s = ',' + cast (@ value as varchar ), @ id_old = @ id end fetch my_cursor into @ id, @ value END insert @ t values (@ id_old, stuff (@ s, 1, 1 ,'')) close my_cursor deallocate my_cursor select * from @ tdrop table tb

The preceding section describes SQL statements for grouping characters. Hope to help you.