SQL classic code-split column Value

Original: zhujian
Adapted from: AI xinjue Luo. Lu Hua (flowers bloom in ice and snow forests in the past 18 years)-12-16 Guangdong and Shenzhen

Table tb:
Id value
----------------------
1 aa, bb
2 aaa, bbb, ccc
To split the value column by id, the results are as follows:
Id value
-------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

1. 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, # B
Where substring (',' + A. [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 ')
Go
Select a. id, B. value
FROM (
SELECT id, [value] = CONVERT (xml, '<root> <v>' + REPLACE ([value], '</v> <v> ') + '</v> </root>') FROM tb
)
Outer apply (
SELECT value = N. v. value ('.', 'varchar (100) ') from a. [value]. nodes ('/root/V') N (v)
) B

Drop table tb

/*
Id value
-----------------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

(5 rows affected)
*/