2000的解法,2005要方法一些,你自己改写吧------------------------------------
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-01-24 08:52:25
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(COL1 int,COL2 nvarchar(7))
Go
Insert into ta
select 1,'a,b,c' union all
select 2,'a,b,c,d' union all
select 3,'a' union all
select 4,'a,b'
Go
--Start
--2000
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM FROM SYSCOLUMNS A,SYSCOLUMNS B
SELECT
A.col1,[VALUE]=SUBSTRING(A.[COL2],B.ID,CHARINDEX(',',A.[COL2]+',',B.ID)-B.ID)
Into #
FROM
ta A,#NUM B
WHERE
CHARINDEX(',',','+A.[COL2],B.ID)=B.ID
select a.col1,a.value + ',' + b.value
from # a full join # b
on a.col1 = b.col1 and a.value > b.value
where a.value + ',' + b.value is not null
drop table #num,#
--Result:
/*
col1
----------- ---------------
1 b,a
1 c,a
1 c,b
2 d,a
2 b,a
2 c,a
2 d,b
2 c,b
2 d,c
4 b,a(所影响的行数为 10 行)*/
--End
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-01-24 08:52:25
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(COL1 int,COL2 nvarchar(7))
Go
Insert into ta
select 1,'a,b,c' union all
select 2,'a,b,c,d' union all
select 3,'a' union all
select 4,'a,b'
Go
--Start
--2000
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM FROM SYSCOLUMNS A,SYSCOLUMNS B
SELECT
A.col1,[VALUE]=SUBSTRING(A.[COL2],B.ID,CHARINDEX(',',A.[COL2]+',',B.ID)-B.ID)
Into #
FROM
ta A,#NUM B
WHERE
CHARINDEX(',',','+A.[COL2],B.ID)=B.ID
select a.col1,a.value + ',' + b.value
from # a full join # b
on a.col1 = b.col1 and a.value > b.value
where a.value + ',' + b.value is not null
drop table #num,#
--Result:
/*
col1
----------- ---------------
1 b,a
1 c,a
1 c,b
2 d,a
2 b,a
2 c,a
2 d,b
2 c,b
2 d,c
4 b,a(所影响的行数为 10 行)*/
--End
DROP TABLE tb
GO
CREATE TABLE tb(f1 INT,f2 VARCHAR(100))
GO
INSERT tb SELECT 1,'a,b,c'
UNION ALL SELECT 2,'a,b,c,d'
UNION ALL SELECT 3,'a'
UNION ALL SELECT 4,'a,b'
GO
SELECT f1,v FROM
(SELECT f1,CAST('<r>' + REPLACE(f2,',','</r><r>') + '</r>' AS XML) f2 FROM tb) a
CROSS APPLY
(
SELECT a.v + ',' + b.v v FROM
(SELECT x.value('.','VARCHAR(10)') v FROM a.f2.nodes('//r') AS t(x)) a,
(SELECT x.value('.','VARCHAR(10)') v FROM a.f2.nodes('//r') AS t(x)) b
WHERE a.v<b.v
) c
WHERE a.f2.exist('//r[2]')='1'
GO/*
1 a,b
1 a,c
1 b,c
2 a,b
2 a,c
2 a,d
2 b,c
2 b,d
2 c,d
4 a,b
*/