ALTER FUNCTION [dbo].[Test_1]
(
@feild nvarchar(50)
)
returns table
AS
RETURN
(
select feild,[1] as "col1",[2] as "col2",[3] as "col3",[4] as "col4",[5] as "col5"
from (select top 100 feild,ROW_NUMBER() over(order by value1) as RowNo, value1
from (SELECT feild,col1 ,col2 ,col3 ,col4 ,col5 from test1 where feild=@feild) a
unpivot (value1 for col in ([col1],[col2],[col3],[col4],[col5])) as unpvt order by value1) b
pivot (sum(value1) for RowNo in ([1],[2],[3],[4],[5])) as pvt
)
ALTER PROCEDURE [dbo].[Test_RowSort]
AS
BEGIN
select Row_Number() over(order by feild) RowNo,feild into #test from test1
declare @i int,@temp nvarchar(50)
select @i=1
while @i<=(select Max(RowNo) from #test)
begin
select @temp= feild from #test where RowNo=@i
set @i=@i+1
update test1 set col1=a.col1,col2=a.col2,col3=a.col3,col4=a.col4,col5=a.col5
from [dbo].[Test_1](@temp) a where test1.feild=@temp
end
END
(
@feild nvarchar(50)
)
returns table
AS
RETURN
(
select feild,[1] as "col1",[2] as "col2",[3] as "col3",[4] as "col4",[5] as "col5"
from (select top 100 feild,ROW_NUMBER() over(order by value1) as RowNo, value1
from (SELECT feild,col1 ,col2 ,col3 ,col4 ,col5 from test1 where feild=@feild) a
unpivot (value1 for col in ([col1],[col2],[col3],[col4],[col5])) as unpvt order by value1) b
pivot (sum(value1) for RowNo in ([1],[2],[3],[4],[5])) as pvt
)
ALTER PROCEDURE [dbo].[Test_RowSort]
AS
BEGIN
select Row_Number() over(order by feild) RowNo,feild into #test from test1
declare @i int,@temp nvarchar(50)
select @i=1
while @i<=(select Max(RowNo) from #test)
begin
select @temp= feild from #test where RowNo=@i
set @i=@i+1
update test1 set col1=a.col1,col2=a.col2,col3=a.col3,col4=a.col4,col5=a.col5
from [dbo].[Test_1](@temp) a where test1.feild=@temp
end
END
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7899089/viewspace-614276/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
3.2.2 数据仓库的架构方式及其比较
下一篇：
數據庫備份
请登录后发表评论
登录
全部评论
