数据库截取函数和行转列问题

发布网友发布时间：2022-04-26 07:36

共3个回答

热心网友时间：2022-06-25 09:06

由于拆分后还需要按顺序组合成，所以使用游标。
declare @a varchar(1000), @b varchar(1000)
declare @seq_a int, @seq_b int
set @seq_a = 1
set @seq_b = 1
create table #a(seq int, val int)
create table #b(seq int, val int)
declare cur cursor for select A, B from tablename
open cur
fetch next from cur into @a, @b
while @@fetch_status = 0
begin
if charindex(',',@a,1) = 0
begin
insert into #a values(@seq_a, cast(@a as int))
set @seq_a = @seq_a + 1
end
else
begin
declare @index_a int,@pos_a int
set @index_a = 1
set @a = @a + ','
set @pos_a = charindex(',',@a,@index_a)
while @pos_a > 0
begin
insert into #a values(@seq_a, cast(substring(@a,@index_a,@pos_a - @index_a) as int))
set @seq_a = @seq_a + 1
set @index_a = @pos_a + 1
set @pos_a = charindex(',',@a,@index_a)
end
end

if charindex(',',@b,1) = 0
begin
insert into #b values(@seq_b, cast(@b as int))
set @seq_b = @seq_b + 1
end
else
begin
declare @index_b int,@pos_b int
set @index_b = 1
set @b = @b + ','
set @pos_b = charindex(',',@b,@index_b)
while @pos_b > 0
begin
insert into #b values(@seq_b, cast(substring(@b,@index_b,@pos_b - @index_b) as int))
set @seq_b = @seq_b + 1
set @index_b = @pos_b + 1
set @pos_b = charindex(',',@b,@index_b)
end
end

fetch next from cur into @a, @b
end
close cur
deallocate cur

select #a.val as A, #b.val as B from #a full join #b on #a.seq = #b.seq
order by isnull(#a.seq, #b.seq)

drop table #a
drop table #b

热心网友时间：2022-06-25 09:06

你这数据什么意思，没看出来怎么拆的，说一下变换的规则
AB
1,23
3,46

到
AB
13
26
31
45追问就是说一个字段里面的数据是9，11，3，14比如有4个数，现在要把这4个数分别存到4行里去
A
9，11，3，14
3，4，6

A
9
11
3
14
3
4
6

热心网友时间：2022-06-25 09:07

SUBSTR($a,0,4),其中4是指长度length-1。