[SQL Server] split column Value

Problem description:
Table TB:
ID values
----------------------
1 aa, BB
2 AAA, BBB, CCC
 
If you want to split the values column, the result is as follows:
ID value
-------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
 
1. Old Solution
Select top 8000
Id = identity (INT, 1, 1)
Into #
From syscolumns A, syscolumns B
Select
A. ID,
Substring (A. [values], B. ID, charindex (',', A. [values] + ',', B. ID)-B. ID)
From tb a, # B
Where substring (',' + A. [values], B. ID, 1) = ','
 
Drop table #
 
-- 2. New Solution
-- Sample Data
Declare @ t table (ID int, [value] varchar (100 ))
Insert @ t select 1, 'aa, BB'
Union all select 2, 'aaa, BBB, CCC'
 
-- Query Processing
Select
A. ID, B. Value
From (
Select ID, [values] = convert (XML,
'<Root> <v>' + Replace ([values], '</V> <v> ') + '</V> </root> ')
From @ t
)
Outer apply (
Select value = n. v. Value ('.', 'varchar (100 )')
From a. [values]. nodes ('/root/V') N (v)
) B
 
/* -- Result
ID value
-------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
 
(5 rows affected)
--*/
Csdn Community post address
Appendix: CLR for merging and splitting. sql2005 examples include:
After installing SQL 2005, the default installation directory is drive:/program files/Microsoft SQL Server/90/samples/engine/programmability/CLR/stringutilities.
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zjcxc/archive/2006/06/09/784276.aspx