34,587
社区成员
发帖
与我相关
我的任务
分享表中某字段按分隔符拆分为多行
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
SELECT * FROM @TB
C1 C2 C3
----------- -------------------- --------------------
1 A a,b,c
2 A a,b
3 B a,b
4 C a,b
想实现结果为：
C1 C2 C3
----------- -------------------- --------------------
1 A a
1 A b
1 A c
2 A a
2 A b
3 B a
3 B b
4 C a
4 C b
...全文
请发表友善的回复…
发表回复
阳泉酒家小当家 2014-01-13
- 打赏
- 举报
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','ab,cd,ef'
--SELECT * FROM @TB
select C1,
C2,
SUBSTRING(t.c3, number ,1) as c3
from @TB t,master..spt_values s
where s.number >=1
and s.type = 'P'
and len(t.c3) >= number
and SUBSTRING(t.c3, number ,1)<>','
/*
C1 C2 c3
1 A a
1 A b
1 A c
1 A d
1 A e
1 A f
*/阳泉酒家小当家 2014-01-13
- 打赏
- 举报
[/quote]
哦，你的意思是，不根据具体的分隔符，只要把每个一个字符串，都进行拆分就可以了是吧，那就更加简单了
木易随风 2014-01-13
- 打赏
- 举报
2楼、3楼的方法，分隔不是按照分隔符拆分的，而是一个字符拆分为一行。
如果数据中
SELECT 1 AS C1,'A','ab,cd,ef'
结果就是:
C1 C2 C3
----------- -------------------- ----
1 A a
1 A b
1 A c
1 A d
1 A e
1 A f
木易随风 2014-01-11
- 打赏
- 举报
我通过下面的方法解决了,看看各位大牛们还有么有更好的方法.
1.创建一个表值函数
create function dbo.StrToCol (@str Varchar(max),@separator varchar(10))
returns @T table(Col varchar(max))
as
Begin
Declare @i int
Set @i=0
Set @str=@str+@separator
While(@i<len(@str))
Begin
Insert @T select substring(@str,@i,charindex(@separator,@str,@i)-@i)
Select @i=charindex(@separator,@str,@i)+1
End
return
End
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
DECLARE @TB2 AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
DECLARE @C1 INT,@C2 VARCHAR(20),@C3 VARCHAR(200)
SELECT * FROM @TB
DECLARE @CNT INT,@I INT
SELECT @CNT=COUNT(1),@I=1 FROM @TB
WHILE @I<=@CNT
BEGIN
SELECT @C1=C1,@C2=C2,@C3=C3 FROM (SELECT ROW_NUMBER() OVER (ORDER BY C1) AS RID,C1,C2,C3 FROM @TB ) A WHERE RID=@I
INSERT INTO @TB2(C1,C2,C3)
SELECT @C1,@C2,COL FROM DBO.StrToCol(@C3,',')
SET @I=@I+1
END
SELECT * FROM @TB2
lzw_0736 2014-01-11
- 打赏
- 举报
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
SELECT c1,c2,b.c3
FROM
(SELECT c1,c2,c3=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(c3)),',','</v><v>')+'</v></root>') FROM @TB) a
OUTER APPLY
(SELECT c3 = C.v.value('.','NVARCHAR(MAX)') FROM a.c3.nodes('/root/v') C(v)) b
sqlkxr 2014-01-11
- 打赏
- 举报
DECLARE @TB TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
select C1,C2,C3=substring(c3,number+1,1) from @tb a,master..spt_values b where substring(c3,number+1,1)<>',' and type='p' and number<=len(c3)-1
--查询结果
（所影响的行数为 4 行）
C1 C2 C3
----------- -------------------- ----
1 A a
1 A b
1 A c
2 A a
2 A b
3 B a
3 B b
4 C a
4 C b
（所影响的行数为 9 行）
阳泉酒家小当家 2014-01-11
- 打赏
- 举报
试试这个：
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
--SELECT * FROM @TB
select C1,
C2,
SUBSTRING(t.c3, number ,CHARINDEX(',',t.c3+',',number)-number) as c3
from @TB t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.c3,s.number,1) = ','
/*
C1 C2 c3
1 A a
1 A b
1 A c
2 A a
2 A b
3 B a
3 B b
4 C a
4 C b
*/
