欄位排序

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-614276/，如需轉載，請註明出處，否則將追究法律責任。