問：
表結構：
name company company_address url1
---------- ---------- -------------------- --------------------
Joe ABC Work Lane abc.com;xyz.com
Jill XYZ Job Street abc.com;xyz.com
寫一個存儲過程等到如下的結果：
name company company_address url1
---------- ---------- -------------------- --------------------
Jill XYZ Job Street abc.com
Jill XYZ Job Street xyz.com
Joe ABC Work Lane xyz.com
Joe ABC Work Lane abc.com
答：
drop table users1
go
create table users1 (name varchar(10),company varchar(10),company_address varchar(20),url1 varchar(20))
insert into users1
select 'Joe ', 'ABC ', 'Work Lane ', 'abc.com;xyz.com '
union all select 'Jill ', 'XYZ ', 'Job Street ', 'abc.com;xyz.com '
drop proc up_test
go
create proc up_test
as
if exists(select 1 from sysobjects where type= 'U ' and name= 'users2 ')
drop table users2
select * into users2
from (
select [name],company,company_address,left(url1,charindex( ';',url1)-1) as url1
from users1
union all
select [name],company,company_address,right(url1,len(url1)-charindex( ';',url1)+1) as url1
from users1) t
order by t.name,t.company,t.company_address
GO
exec up_test
select * from users2
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。