自定义函数

Question

假设我一个表有一行数据如下：
A       B       C        D       E        F       G
2.2    3.3      4.4      5.5    1.1       7.3    8.9
自定义一个函数对这一行数据进行排序：使其变成这样的形式
A       B       C        D       E        F       G
2.2    3.3      4.4      5.5    1.1       7.3    8.9
6       5        4       3       6        2       1

Answer 1

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([A] [numeric](2,1),[B] [numeric](2,1),[C] [numeric](2,1),[D] [numeric](2,1),[E] [numeric](2,1),[F] [numeric](2,1),[G] [numeric](2,1))
INSERT INTO [tb]
SELECT '2.2','3.3','4.4','5.5','1.1','7.3','8.9'-->SQL查询如下:
SELECT * FROM [tb]
UNION ALL
SELECT *
FROM (
SELECT rn = ROW_NUMBER()OVER(ORDER BY val DESC), col 
FROM tb 
UNPIVOT(val FOR col IN (a, b, c, d, e, f, g))b
) t
PIVOT (MAX(rn) FOR col IN(A,B,C,D,E,F,G)) c
/*
A                                       B                                       C                                       D                                       E                                       F                                       G
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2.2                                     3.3                                     4.4                                     5.5                                     1.1                                     7.3                                     8.9
6.0                                     5.0                                     4.0                                     3.0                                     7.0                                     2.0                                     1.0(2 行受影响)
*/