34,576
社区成员
发帖
与我相关
我的任务
分享
/*
sysbase 数据库
如何將现有的表1转换为表2 ,谢谢~
最好写个存储工程!
表1:
CNO MESSAGE
-----------------------------------------------------
001 2021 |AA |20070902|
002 2022 |CC |20070905|
003 2058 |BB |20070906|
表2:
CNO COLUMN1 COLUMN2 COLUMN3
-------------------------------------------------------------
001 2021 AA 20070902
002 2022 CC 20070905
003 2058 BB 20070906
create table t(cno varchar(5),message varchar(50))
insert into t select '001','2021|AA|20070902|'
insert into t select '002','2022|CC|20070905|120|'
insert into t select '003','2058|BB|'
go
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
select
cno ,
dbo.f_GetStr (message,1,'|') as col1,
dbo.f_GetStr (message,2,'|') as col2,
dbo.f_GetStr (message,3,'|') as col3,
dbo.f_GetStr (message,4,'|') as col4
from
t
/*
001 2021 AA 20070902
002 2022 CC 20070905 120
003 2058 BB
*/
/*
功能:实现split功能的函数
*/
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
create table tk(cno varchar(5),message varchar(50))
insert into tk select '001','2021|AA|20070902|'
insert into tk select '002','2022|CC|20070905|120|'
insert into tk select '003','2058|BB|'
go
create proc GetCol
as
set nocount on
declare @max int,@i int
declare @sql varchar(8000)
set @i=1
select @max=max(len(message)-len(replace(message,'|',''))) from tk
select cno,message+replicate('|',@max-len(message)+len(replace(message,'|',''))) message into tkx from tk
while @i<=@max
begin
select @sql=isnull(@sql+',','')+'col'+ltrim(@i)+' varchar(50)',@i=@i+1
end
set @sql='create table tkxx(cno varchar(5),'+@sql+')'
exec(@sql)
set @i=1
insert tkxx(cno) select cno from tkx
while @i<=@max
begin
set @sql='
declare @a table(cno varchar(5),head varchar(50));
insert @a select cno,left(message,charindex(''|'',message)-1) from tkx;
update tkx set message=stuff(message,1,charindex(''|'',message),'''');
update tkxx set col'+ltrim(@i)+'=head from @a a where tkxx.cno=a.cno;'
exec(@sql)
set @i=@i+1
end
select * from tkxx
drop table tkx,tkxx
go
exec getCol
--result
/*
cno col1 col2 col3 col4
----- ------------------------------ ------------------------------ ------------------------------ ------------------------------
001 2021 AA 20070902
002 2022 CC 20070905 120
003 2058 BB
*/
没用我的这个试一下吗?
用SQL格式发个最终版本:
create table tk(cno varchar(5),message varchar(50))
insert into tk select '001','2021|AA|20070902|'
insert into tk select '002','2022|CC|20070905|120|'
insert into tk select '003','2058|BB|'
--创建函数:
create function sssss(@cno varchar(10))
returns varchar(2000)
as
begin
declare @message varchar(2000)
declare @i int,@num int,@c int
set @i=1
select @message=message from tk where cno=@cno
select @num=max(len(replace(message,' ',''))-len(replace(replace(message,' ',''),'|',''))) from tk
set @c=len(replace(@message,' ',''))-len(replace(replace(@message,' ',''),'|',''))
set @message=@message+replicate(' |',@num-@c)
while(charindex('|',@message)>0)
begin
set @message=stuff(@message,charindex('|',@message),1,''' columns'+cast(@i as varchar)+',''')
set @i=@i+1
end
set @message=left(@message,len(@message)-2)
return ''''+@cno+''' cno,'''+@message
end
go
--创建存储过程
create proc wsp
as
declare @sql varchar(8000)
select @sql=isnull(@sql+' union select ','select ')+dbo.sssss(cno) from tk
exec(@sql)
--执行存储过程
exec wsp
create table tb(CNO varchar(10), MESSAGE varchar(30))
insert into tb values('001', '2021|AA|20070902|')
insert into tb values('002', '2022|CC|20070905|')
insert into tb values('003', '2058|BB|20070906|')
go
select cno ,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),3) column1,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),2) column2,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),1) column3
from tb
drop table tb
/*
cno column1 column2 column3
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
001 2021 AA 20070902
002 2022 CC 20070905
003 2058 BB 20070906
(3 行受影响)
*/