如何实现横向聚合

问题描述：

有表tb，数据如下

A 1 A 2 A 3 A 4 A 5

1? 2? 5? 3? 4

2? 2? 3? 4? 5

0? 3? 4? 2? 5

?

如何输出

A 1 A 2 A 3 A 4 A 5 最大 最小? 5以上个数

1? 2? 5? 3? 4?? 5??? 1?????? 1

2? 2? 3? 4? 5?? 5??? 2?????? 1

0? 3? 5? 2? 6?? 6??? 0?????? 2

SQL Server的聚合函数是在列上的，所以可以写自定义的函数，也可以想办法把列变为行，再用聚合函数处理

--测试数据

DECLARE @t TABLE(A1 int, A2 int, A3 int, A4 int, A5 int)

INSERT @t SELECT 1,2,5,3,4

UNION ALL SELECT 2,4,5

UNION ALL SELECT 0,5

?

--查询

SELECT *,

??? [min] = (

??????? SELECT MIN(v) FROM(

??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3

??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5

??????? )B),

??? [max] = (

??????? SELECT MAX(v) FROM(

??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3

??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5

??????? )B),

??? [count>=5] = (

??????? SELECT COUNT(*) FROM(

??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3

??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5

??????? )B WHERE v>=5)

FROM @t A

A 1 ????????? A 2 ????????? A 3 ????????? A 4 ????????? A 5????????? min???????? max???????? count>=5

----------- ----------- ----------- ----------- ----------- ----------- -----------

1?????????? 2?????????? 5?????????? 3?????????? 4?????????? 1?????????? 5?????????? 1

2?????????? 2?????????? 3?????????? 4?????????? 5?????????? 2?????????? 5?????????? 1

03425051