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函数去掉一个“,”并在后面又加了一个“,”,原因是使代码可以更清晰。

        代码经我测试已无误。
posted @ 2009-05-22 11:38  夜色狼  阅读(572)  评论(0编辑  收藏  举报