SQL实现交叉表的方法代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL实现交叉表的方法
作者:疯狂的流浪 / 发布于2011/1/10/ 833
<div>--交叉一般来讲是分组统计的一种，形式更复杂，显示更清淅，但数据库本身并没有提供实---现交叉表的功能，自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉，而--且思路也要清淅，本例以PUBS.DBO.SALES表的数据做样本：
CREATE PROCEDURE UP_TEST(
@T1 VARCHAR(30),@T2 VARCHAR(30),
@T3 VARCHAR(30),@T4 VARCHAR(30)) AS
--T1 表名,T2,T3是交叉表的两上分类字段,T4是汇总字段
--T2是行字段，T3列字段
BEGIN
DECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)
SELECT @SQL='SELECT DISTINCT <a href="mailto:'+@T3+'">'+@T3+'</a> FROM <a href="mailto:'+@T1">'+@T1</a>
CREATE TABLE #FIELD(FIELD VARCHAR(30))
--将列字段提取到临时表#FIELD中
INSERT INTO #FIELD EXEC(@SQL)
SELECT @SQL='CREATE TABLE CROSS_TEST(<a href="mailto:'+@T2+'">'+@T2+'</a> VARCHAR(30),'
DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD
OPEN CUR_FIELD
FETCH CUR_FIELD INTO @FIELD
WHILE @@FETCH_STATUS=0 BEGIN
 SELECT @FIELD='['+@FIELD+']'
SELECT @SQL=@SQL+@FIELD+' DECIMAL(8,2) DEFAULT 0,'
 FETCH CUR_FIELD INTO @FIELD
END
SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1)+')'
--创建临时交叉表CROSS_TEST
EXEC(@SQL)
SELECT @SQL='INSERT INTO CROSS_TEST(<a href="mailto:'+@T2+'">'+@T2+'</a>) SELECT DISTINCT <a href="mailto:'+@T2+'">'+@T2+'</a> FROM <a href="mailto:'+@T1">'+@T1</a>
--将行数据存入交叉表#CROSS_TEST
EXEC(@SQL)
--创建分组数据表TEMP
SELECT @SQL='CREATE TABLE TEMP(<a href="mailto:'+@T2+'">'+@T2+'</a> VARCHAR(30),'+@T3+' VARCHAR(30),'+@T4+' DECIMAL(8,2))'
EXEC(@SQL) 
--将交叉汇总数据放入交叉表
SELECT @SQL='SELECT <a href="mailto:'+@T2+','+@T3+'">'+@T2+','+@T3+'</a>, SUM(QTY) QTY FROM <a href="mailto:'+@T1">'+@T1</a> +' GROUP BY <a href="mailto:'+@T2+','+@T3">'+@T2+','+@T3</a>
INSERT INTO TEMP EXEC(@SQL)
--将汇总数据写入交叉表
DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP
DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)
OPEN CUR_SUM
FETCH CUR_SUM INTO @F1,@F2,@QTY
WHILE @@FETCH_STATUS=0 BEGIN
 SELECT @F2='['+@F2+']',@Q1=CAST(@QTY AS VARCHAR(30))
 SELECT @SQL='UPDATE CROSS_TEST SET <a href="mailto:'+@F2+'='+@Q1+'">'+@F2+'='+@Q1+'</a> WHERE <a href="mailto:'+@T2+'='''+@F1+''''">'+@T2+'='''+@F1+''''</a>
 EXEC(@SQL)
 FETCH CUR_SUM INTO @F1,@F2,@QTY
END
CLOSE CUR_SUM
SELECT * FROM CROSS_TEST
DROP TABLE TEMP 
DROP TABLE CROSS_TEST
DROP TABLE #FIELD
END
--------------------------------------------------------
EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'
/*说明：字段加中括号为了处理字段中含有特殊字符，值得注意得是要实现交叉表的表必须有两个分类，本例只支持分类字段的数据类型是字符型的，最大的问题就是高亮显示这行的WHERE条件啦，字符类型字段查询时条件必须加单引号，如果是数值类型就可以直接写，所以数值类型的分类字段更容易实现一些，更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息，本例就没有实现，最后一点工作大家自己练练手吧。*/</div>
试试其它关键字
　交叉表　
同语言下
可能有用的
疯狂的流浪贡献的其它代码(8)