交叉表在SQL Server中的应用

在项目的实际开发过程中，我们有时会遇到交叉表的问题， 举个例子。我们假设有一个表T_ENT_EXHIBITION_SEATS ， 结构及演示数据如下：

 

ENT_NAME	EXHIBITION_NAME	EXHIBITION_SEATS
ENTA	105-1	5
ENTA	105-2	7
ENTA	106-1	13
ENTB	105-2	2
ENTC	105-1	18
ENTC	106-2	7
ENTD	105-2	16
ENTE	106-1	3

 

依据以上的数据表及数据，我们需要让EXHIBITION_NAME的数据成为列，得到如下的结构和数据：

 

ENT_NAME	105-1	105-2	106-1	106-2
ENTA	5	7	13	0
ENTB	0	2	0	0
ENTC	18	0	0	7
ENTD	0	16	0	0
ENTE	0	0	3	0

 

在SQL Server中，根据不同的版本，我们可以有不同的解决方案，以SQL Server 2005作为分水岭，我们先说SQL Server 2000如何来解决这个问题。通常我们说交叉表产生的数据列(像EXHIBITION_NAME) 可以是一定范围不变的数据，还有是可以维护的动态数据，前者生成的列是固定的，后者随着维护数据的增加或减少是可以变化的，不同的情况处理方法不一样，先解决前者，相对比较简单，我们可以利用CASE语句来完成：

SELECT ENT_NAME,

       ISNULL(CASE WHEN EXHIBITION_NAME = '105-1'

            THEN EXHIBITION_SEATS

       END, 0) AS EXHIBITION_NAME,

       ISNULL(CASE WHEN EXHIBITION_NAME = '105-2'

            THEN EXHIBITION_SEATS

       END, 0) AS EXHIBITION_NAME,

       ISNULL(CASE WHEN EXHIBITION_NAME = '106-1'

            THEN EXHIBITION_SEATS

       END, 0) AS EXHIBITION_NAME,

       ISNULL(CASE WHEN EXHIBITION_NAME = '106-2'

            THEN EXHIBITION_SEATS

       END, 0) AS EXHIBITION_NAME

FROM T_ENT_EXHIBITION_SEATS

ORDER BY ENT_NAME ASC   

如果数据列是变化的，那么就需要利用动态SQL来完成了：    

DECLARE @DynamicPIVOT NVARCHAR(4000)

 

SELECT @DynamicPIVOT = 'SELECT ENT_NAME'

 

SELECT @DynamicPIVOT = @DynamicPIVOT +

                       ', ISNULL(SUM(CASE WHEN EXHIBITION_NAME = ''' + EXHIBITION_NAME +

                       ''' THEN EXHIBITION_SEATS END), 0) AS ''' + EXHIBITION_NAME + ''''

FROM T_ENT_EXHIBITION_SEATS

GROUP BY EXHIBITION_NAME

ORDER BY EXHIBITION_NAME ASC

 

SET @DynamicPIVOT = @DynamicPIVOT + ' FROM T_ENT_EXHIBITION_SEATS GROUP BY ENT_NAME '

 

EXECUTE(@DynamicPIVOT)

到了SQL Server 2005或者SQL Server 2008，微软为我们提供了PIVOT来完成这个工作，尤其对于固定数据列的情形，尤为简单：

SELECT ENT_NAME,

       ISNULL([105-1], 0) AS [105-1],

       ISNULL([105-2], 0) AS [105-2],

       ISNULL([106-1], 0) AS [106-1],

       ISNULL([106-2], 0) AS [106-2]

FROM T_ENT_EXHIBITION_SEATS

PIVOT (

 SUM(EXHIBITION_SEATS)

 FOR EXHIBITION_NAME IN ([105-1], [105-2], [106-1], [106-2])

) AS PVT

ORDER BY ENT_NAME;

需要注意的是，查询的数据列必须和IN明确的范围对应（顺序无关）。至于，动态数据列，依然采用动态SQL来完成。具体如何写，这里就不深究了。

关于PIVOT的使用可以参考以下的链接：

http://technet.microsoft.com/en-us/library/ms177410(SQL.90).aspx