数据库环境:SQL SERVER2008R2
需求：用SQL实现如下2个图中的矩阵。
图1和图2都是行列转换的另一个变形，下面直接贴上SQL脚本。
图1的SQL实现
/*利用系统的数字辅助表，生成1-25及每连续5个数一组的组号(从1开始)*/
WITH x0
AS ( SELECT ( number - 1 ) / 5 + 1 AS cn ,
number AS seq
FROM master..spt_values
WHERE number <= 25
AND number >= 1
AND type = 'P'
),/*新增一列，按组内降序排序，及在同组内从大到小排序*/
x1
AS ( SELECT TOP 25
cn ,
seq ,
ROW_NUMBER() OVER ( ORDER BY cn, seq DESC ) dseq
FROM x0
ORDER BY cn ,
seq
)
/*如果是单行号，则升序；否则，降序*/
SELECT MAX(CASE seq % 5
WHEN 1 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS A ,
MAX(CASE seq % 5
WHEN 2 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS B ,
MAX(CASE seq % 5
WHEN 3 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS C ,
MAX(CASE seq % 5
WHEN 4 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS D ,
MAX(CASE seq % 5
WHEN 0 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS E
FROM x1
GROUP BY cn
图2的SQL实现
/*利用系统的数字辅助表，生成1-25及每连续5个数一组的组号(从1开始)*/
WITH x0
AS ( SELECT ( number - 1 ) / 5 + 1 AS cn ,
number AS seq
FROM master..spt_values
WHERE number <= 25
AND number >= 1
AND type = 'P'
),/*新增一列，按组内降序排序，及在同组内从大到小排序*/
x1
AS ( SELECT TOP 25
cn ,
seq ,
ROW_NUMBER() OVER ( ORDER BY cn, seq DESC ) dseq
FROM x0
ORDER BY cn ,
seq
),/*按对5求余的规则新生成一个组号，根据原组号取整组的数据*/
x2
AS ( SELECT seq % 5 AS sno ,
CASE cn
WHEN 1 THEN seq
END AS A ,
CASE cn
WHEN 2 THEN dseq
END AS B ,
CASE cn
WHEN 3 THEN seq
END AS C ,
CASE cn
WHEN 4 THEN dseq
END AS D ,
CASE cn
WHEN 5 THEN seq
END AS E
FROM x1
)
/*按新组号分组，排序*/
SELECT MAX(A) AS A ,
MAX(B) AS B ,
MAX(C) AS C ,
MAX(D) AS D ,
MAX(E) AS E
FROM x2
GROUP BY sno
ORDER BY A
当然，实现的方法不局限于上述2种。欢迎提出更好的解决思路。