CREATE TABLE TEST(ID INT,value VARCHAR(10)) INSERT TEST SELECT 1,'A,B' UNION ALL SELECT 2,'B' UNION ALL SELECT 2,'C,D' --生成临时表 SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b --分拆 SELECT A.id, 'value'=SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id) FROM test A, # B WHERE SUBSTRING(',' + A.[value], B.id, 1) = ',' --删除测试环境 DROP TABLE #
CREATE TABLE TEST(ID INT,value VARCHAR(10)) INSERT TEST SELECT 1,'A,B' UNION ALL SELECT 2,'B' UNION ALL SELECT 2,'C,D' DECLARE @s VARCHAR(8000) SELECT @s=ISNULL(@s,'') + ',' + value FROM Test SELECT @s= REPLACE(STUFF(@s,1,1,''),',',''' UNION SELECT ''') SELECT @s='SELECT V=''' + @s + '''' SELECT @s='SELECT ID,V FROM Test INNER JOIN ('+ @s +')t ON CHARINDEX('',''+V+'','','',''+Value+'','')>0' EXEC(@s) DROP TABLE Test
DECLARE @s VARCHAR(8000) SELECT @s=ISNULL(@s,'') + ',' + value FROM Test SELECT @s='SELECT ID,V FROM Test INNER JOIN (SELECT V=''' + REPLACE(STUFF(@s,1,1,''),',',''' UNION SELECT ''') + ''')t ON CHARINDEX('',''+V+'','','',''+Value+'','')>0' EXEC(@s)
CREATE TABLE TEST(ID INT,value VARCHAR(10))
INSERT TEST SELECT 1,'A,B'
UNION ALL SELECT 2,'B'
UNION ALL SELECT 2,'C,D'
--生成临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
--分拆
SELECT A.id, 'value'=SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM test A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','
--删除测试环境
DROP TABLE #
INSERT TEST SELECT 1,'A,B'
UNION ALL SELECT 2,'B'
UNION ALL SELECT 2,'C,D'
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s,'') + ',' + value FROM Test
SELECT @s= REPLACE(STUFF(@s,1,1,''),',',''' UNION SELECT ''')
SELECT @s='SELECT V=''' + @s + ''''
SELECT @s='SELECT ID,V FROM Test INNER JOIN ('+ @s +')t ON CHARINDEX('',''+V+'','','',''+Value+'','')>0'
EXEC(@s)
DROP TABLE Test
SELECT @s=ISNULL(@s,'') + ',' + value FROM Test
SELECT @s='SELECT ID,V FROM Test INNER JOIN (SELECT V=''' + REPLACE(STUFF(@s,1,1,''),',',''' UNION SELECT ''') + ''')t ON CHARINDEX('',''+V+'','','',''+Value+'','')>0'
EXEC(@s)