°Ù·ÖSQLÓï¾äÇó½â - MS-SQL Server / »ù´¡Àà

Êý¾Ý±íÈçÏÂ
id name
1 c;c++;java
2 python

Ï£Íûͨ¹ýSQL²éѯµÃµ½ÈçϽá¹û
id name
1 c
1 c++
1 java
2 python 

Íû¸÷λ´óÄò»Áߴͽ̣¬Ð¡µÜ²»Ê¤¸Ð¼¤£¡

---

SQL code:
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb (id INT,col VARCHAR(30))
INSERT INTO tb VALUES (1,'aa,bb')
INSERT INTO tb VALUES (2,'aaa,bbb,ccc')
go
--1.2000/2005ͨÓ÷½·¨
SELECT
a.id,
col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)
from tb a
JOIN master..spt_values b
ON b.type='p'
--AND SUBSTRING(','+a.col,b.number,1)=',' --ÓôËÌõ¼þ»òÏÂÃæµÄÌõ¼þ¾ù¿É
AND CHARINDEX(',',','+a.col,number)=number

--2.2005ÒÔÉÏз½·¨:
SELECT a.id,b.col
from (SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS xml) from tb) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') from a.col.nodes('/v') AS T(C)) b

---

--sql 2000
SQL code:
create table tb(id int,name varchar(10))
insert into tb values(1, 'c;c++;java')
insert into tb values(2, 'python')
go


SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # from syscolumns a, syscolumns b

SELECT A.id, name = SUBSTRING(A.[name], B.i