sql 一列转多列

[y8932809]

如表为:
列
a*b*c*d
a1*b1*c1*d1

转换成

列1 列2 列3 列4
a b c d
a1 b1 c1 d1

[y8932809]

根据*号分隔成多列

[中国风]

use Tempdb
go
--> --> 
 
declare @T table([列] nvarchar(11))
Insert @T
select N'a*b*c*d' union all
select N'a1*b1*c1*d1'
 
SELECT PARSENAME([列],4) AS [列1]
,PARSENAME([列],3) AS [列2]
,PARSENAME([列],2) AS [列3]
,PARSENAME([列],1) AS [列4]

FROM (SELECT [列]=REPLACE([列],'*','.') FROM  @T) AS a
/*

列1	列2	列3	列4
a	b	c	d
a1	b1	c1	d1*/

[y8932809]

引用 2 楼 roy_88 的回复:

use Tempdb
go
--> --> 
 
declare @T table([列] nvarchar(11))
Insert @T
select N'a*b*c*d' union all
select N'a1*b1*c1*d1'
 
SELECT PARSENAME([列],4) AS [列1]
,PARSENAME([列],3) AS [列2]
,PARSENAME([列],2) AS [列3]
,PARSENAME([列],1) AS [列4]

FROM (SELECT [列]=REPLACE([列],'*','.') FROM  @T) AS a
/*

列1	列2	列3	列4
a	b	c	d
a1	b1	c1	d1*/

感谢，已经解决