现有如下表结构
------------------
|userid roleid |
|------------------
|38 14| |
|211 64|48| |
|212 14| |
|213 14| |
|214 14| |
|215 14| |
|224 14| |
-------------------
要求把64|48| 变成2条记录,其他的保留,如下
------------------
|userid roleid |
|------------------
|38 14| |
|211 64| |
|211 48| |
|212 14| |
|213 14| |
|214 14| |
|215 14| |
|224 14| |
-------------------
前面可以加一列ID,自动编号
谢谢了!
------------------
|userid roleid |
|------------------
|38 14| |
|211 64|48| |
|212 14| |
|213 14| |
|214 14| |
|215 14| |
|224 14| |
-------------------
要求把64|48| 变成2条记录,其他的保留,如下
------------------
|userid roleid |
|------------------
|38 14| |
|211 64| |
|211 48| |
|212 14| |
|213 14| |
|214 14| |
|215 14| |
|224 14| |
-------------------
前面可以加一列ID,自动编号
谢谢了!
INSERT u (userid,roleid)
SELECT 38, '14| |'
UNION SELECT 211, '64|48| |'
UNION SELECT 212, '14| |'
UNION SELECT 213, '14| |'
UNION SELECT 214, '14| |'
UNION SELECT 215, '14| |'
UNION SELECT 224, '14| |'DECLARE @r VARCHAR(1024)
SET @r=''
SELECT @r=RTRIM(@r) + RTRIM(roleid)FROM u
SELECT a.userid,b.roleid,IDENTITY(INT) NewID INTO #TMP FROM u a
INNER JOIN
(SELECT tempColumn as roleid FROM dbo.Split(@r,'|') WHERE RTRIM(tempColumn)<>'') b
ON CHARINDEX('|'+b.roleid + '|','|'+a.roleid)>0 GROUP BY a.userid,b.roleid
TRUNCATE TABLE u
--ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
ALTER TABLE u ADD NewID INT NULL
INSERT u SELECT * FROM #tmp
SELECT * FROM u
DROP TABLE #tmpDROP TABLE u附split函數
CREATE Function Split(@Sql varchar(8000),@Splits varchar(100))
returns @temp Table (tempColumn varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+len(@Splits),Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ''
Insert @temp Values (@Sql)
Return
End
主要是自定义函数在SQL Server中的参数不能从表中定义。在2005种可以直接由数据表得到。CREATE FUNCTION dbo.f_newRow (@userid int, @roleid varchar(100))
RETURNS @ret table(userid int,roleid varchar(100))
AS
begin
while charindex('|',@roleid)>0
begin
insert @ret
select @userid,substring(@roleid,1,charindex('|',@roleid)-1)
set @roleid=substring(@roleid,charindex('|',@roleid)+1,len(@roleid)-charindex('|',@roleid))
end
insert @ret select @userid,@roleid
return
end
GOcreate table t(userid int,roleid varchar(100))
insert t select 38,'14'
union all select 211,'64|48'
union all select 211,'14'
union all select 213,'65|412'
GOdeclare @userid int, @roleid varchar(100)
declare Cursor1 cursor for
select userid,roleid from t where charindex('|',roleid)>0
open Cursor1
fetch next from Cursor1 into @userid,@roleid
while @@Fetch_Status=0
begin---------------------------------------------------delete from t where userid=@userid and roleid=@roleid
insert t
select * from dbo.f_newRow(@userid,@roleid)fetch next from Cursor1 into @userid,@roleid
end------------------------------------------------------
close Cursor1
deallocate Cursor1select * from t/*
userid roleid
----------- -----------------------------------------
38 14
211 64
211 14
213 65
211 48
213 412
*/
drop function dbo.f_newRow
drop table t