SQL交叉表的应用与实现

SQL交叉表的应用与实现 · 编程学习·编程技术资料网

2009-05-15 22:37

静态的语句：

问题的提出：       

如何把以下形式
FItemID      FName          FStockID     FDeptID         FAuxQty                                               
----------- --------
337          生产部        598          327          1000.0
337          包装车间      624          605          1000.0
339          包装车间      598          605          200.0
.               .          
.               .  
.               .
转为这个形式
FItemID     生产部       包装车间 FStockID
----------- --------
337         1000                   .       598
337         0            1000       .       624
339         0            200        .       598

        从上面我们可以看出，得到的结果就是要实现一个交叉表，要解决以上问题，最简单的方法就是用CASE函数。让我们先来看看CASE函数的释义：

CASE 函数是特殊的 Transact-SQL 表达式，它允许按列值显式可选值。数据中的更改是临时的，没有对数据进行永久更改。例如，CASE 函数可以在 state 列中有 CA 值的行的查询结果集内显示 California。

CASE 函数包含：

        CASE 关键字。


        需要转换的列名称。


        指定要搜索的表达式的 WHEN 子句和指定要替换它们的表达式的 THEN 子句。


        END 关键字。


        可选的、定义 CASE 函数别名的 AS 子句。

        由此，我们得出以上问题的解法：

SELECT FItemID, (CASE FName WHEN '生产部' THEN FAuxQty ELSE 0 END) AS '生产部',
       (CASE FName WHEN '包装车间' THEN FAuxQty ELSE 0 END) AS '包装车间',
       FStockID, FDeptID
FROM testtable

        以上只是个简单的示例，只要我们理解的CASE函数的正确用法，要写出复杂的交叉表，也是轻而易举的事了。

动态的SQL语句

在前一篇SQL交叉表的实现 http://www.cnblogs.com/bonny.wong/archive/2005/01/22/95911.html 中，我举了一个静态实现SQL交叉表的简单例子，结果遭到几位朋友的批评，不得已，只有厚着脸皮，重新写了一个动态实现交叉表的代码，以答谢各位热心的观众。

        示例仍取前一篇中之简单例子以便说明，解决代码如下：

declare @sqlText nvarchar(2000)

select @sqlText='select FItemID,'

select @sqlText=@sqltext+'(CASE FName WHEN '''+FName+''' THEN FAuxQty ELSE 0 END) AS ''' +FName+
    ''',' from (select distinct FName from test) as a

select @sqlText=left(@sqlText,Len(@sqlText)-1)+',FStockID,FDeptID from test'
print @sqltext
exec(@sqlText)
go

        这里需注意，第一句select不可以和第二句select合并，不信你可以试试，原因吗？暂时不说，你自己也可以想想。
         在第三个slect语句中我用了left函数去掉一个“，”并在后面又加了一个“，”，原因是使代码可以更清晰。

        代码经我测试已无误。