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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.