一道預存程序面試題

一道預存程序面試題
users1

　　name    company    company_address   url1
　　Joe     ABC        Work Lane        abc.com;xyz.com
　　Jill    XYZ        Job Street       abc.com;xyz.com
寫預存程序來建立新表
users2
　　name company    company_address    url1

　　Joe  ABC     Work Lane          abc.com
    Joe         ABC        Work Lane          xyz.com
　　Jill        XYZ        Job Street         abc.com
    Jill        XYZ        Job Street         xyz.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))
from users1)t
order by name,company,company_address
GO
exec up_test

select * from users2
/*
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

（所影響的行數為 4 行）
*/

============================================================================================================

--應該寫function,然後根據;拆分紀錄

GO
--FUNCTION
Create    FUNCTION SplitList
(@separator char(1), @List varchar(8000))
RETURNS @ReturnTable TABLE(ListItem varchar(1000) COLLATE Database_Default)
AS
BEGIN
DECLARE @Index int
DECLARE @NewText varchar(8000)
IF @List = null
RETURN
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
    END
 
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
        
     RETURN
END

GO
--測試數據
create table users1(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
insert into users1 select    'Joe'   ,'ABC'  ,'Work Lane'  ,'abc.com;xyz.com'
insert into users1 select   'Jill'   ,'XYZ'  ,'Job Street' , 'abc.com;xyz.com'

create table users2(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
GO
--存儲過程

Create procedure dbo.usp_test
AS

declare @name  varchar(10) ,  @company  varchar(20),  @company_address  varchar(20), @url1 varchar(100)
declare c1 cursor for
select * from users1
open c1
fetch next from c1 into @name,@company,@company_address,@url1
while @@fetch_status=0
begin
  insert into users2
   select @name,@company,@company_address,ListItem from dbo.SplitList(';',@url1) T
  fetch next from c1 into @name,@company,@company_address,@url1
end
close c1
deallocate c1

GO

exec dbo.usp_test
select * from users2
/*
name    company      company_address           url1
Joe ABC  Work Lane            abc.com
Joe ABC  Work Lane            xyz.com
Jill XYZ  Job Street         abc.com
Jill XYZ  Job Street          xyz.com

*/

drop table users1,users2
drop proc usp_test
drop function splitlist