SQL merge column value and split column Value

The column value table structure is merged. The data is as follows: ID value ----- ------ 1 aa1 BB2 aaa2 bbb2 CCC. Expected result: ID values ------ ----------- 1 aa, BB2 AAA, BBB, CCC: group by ID, calculate the sum of values (string addition) 1. the old solution (in SQL Server 2000, only functions can be used .) -- 1. 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 (8000) asbegin declare @ r varchar (8000) set @ r = ''select @ r = @ r + ', '+ value from TB where id = @ ID return stuff (@ r, 1, 1, '') endgo -- call the function selec T ID, value = DBO. f_str (ID) from TB group by iddrop table tbdrop function DBO. f_str/* id value ----------- --------- 1 aa, BB2 AAA, BBB, CCC (two rows affected) */-- 2, another function. 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 ') go -- create a merged function create function f_hb (@ id int) Returns varchar (8000) asbegin 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) endgo -- call a UDF to get the result: select distinct ID, DBO. f_hb (ID) as value from tbdrop table tbdrop function DBO. f_hb/* id value ----------- 1 aa, BB2 AAA, BBB, CCC (two rows affected) */2. new Solution (using outer in SQL Server 2005 Apply .) 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 ') go -- Query Processing select * from (select distinct ID from TB) A outer apply (select [values] = stuff (replace (select value from TB n where id =. ID for XML auto), '<n value = "', ','), '"/>', ''), 1, 1 ,'')) ndrop table TB/* ID values ----------- --------- 1 aa, BB2 AAA, BBB, CCC (2 rows affected) */-- 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') goselect ID, [values] = stuff (select ', '+ [value] from TB t where id = Tb. ID for XML Path (''), 1, 1,'') from tbgroup by ID/* ID values --------- ---------------------- 1 aa, BB2 AAA, BBB, CCC (2 row (s) affected) */drop table TB split column values have table TB, as shown below: ID value ----------- 1 aa, BB2 AAA, BBB, CCC to be based on ID, after splitting the value column, the result is as follows: ID value ----------- -------- 1 aa1 BB2 aaa2 bbb2 ccc1. the old solution (SQL Server 2000) Select top 8000 id = identity (INT, 1, 1) into # From syscolumns A, syscolumns B select. ID, substring (. [values], B. ID, charindex (',',. [values] + ',', B. ID)-B. ID) from tb a, # bwhere substring (',' +. [values], B. ID, 1) = ', 'drop table #2. new Solution (SQL Server 2005) Create Table Tb (ID int, value varchar (30) insert into TB values (1, 'aa, BB ') insert into TB values (2, 'aaa, BBB, CCC ') goselect. ID, B. valuefrom (select ID, [value] = convert (XML, '<root> <v>' + Replace ([value], ',', '</V> <v>') + '</V> </root>') from TB) aouter apply (select value = n. v. value ('. ', 'varchar (100)') from. [value]. nodes ('/root/V') N (v) bdrop table TB/* id value -------------------------------------- 1 aa1 BB2 aaa2 bbb2 CCC (5 rows affected )*/