SQL實現(xiàn)交叉表的方法

悟靜 2012-02-25

展開全文

--交叉一般來講是分組統(tǒng)計的一種，形式更復(fù)雜，顯示更清淅，但數(shù)據(jù)庫本身并沒有提供實---現(xiàn)交叉表的功能，自己創(chuàng)建交叉表不僅要對過程、游標(biāo)、臨時表、動態(tài)SQL等非常熟悉，而--且思路也要清淅，本例以PUBS.DBO.SALES表的數(shù)據(jù)做樣本： 
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 '+@T3+' FROM '+@T1 
 CREATE TABLE #FIELD(FIELD VARCHAR(30)) 
 --將列字段提取到臨時表#FIELD中 
 INSERT INTO #FIELD EXEC(@SQL) 
 SELECT @SQL='CREATE TABLE CROSS_TEST('+@T2+' 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)+')'
 --創(chuàng)建臨時交叉表CROSS_TEST 
 EXEC(@SQL) 
 SELECT @SQL='INSERT INTO CROSS_TEST('+@T2+') SELECT DISTINCT '+@T2+' FROM '+@T1 
--將行數(shù)據(jù)存入交叉表#CROSS_TEST 
EXEC(@SQL) 
 --創(chuàng)建分組數(shù)據(jù)表TEMP 
 SELECT @SQL='CREATE TABLE TEMP('+@T2+' VARCHAR(30),'+@T3+' VARCHAR(30),'+@T4+' DECIMAL(8,2))'
 EXEC(@SQL)  
 --將交叉匯總數(shù)據(jù)放入交叉表 
 SELECT @SQL='SELECT  '+@T2+','+@T3+', SUM(QTY) QTY FROM '+@T1 +' GROUP BY '+@T2+','+@T3 
 INSERT INTO TEMP EXEC(@SQL) 
 --將匯總數(shù)據(jù)寫入交叉表 
 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 '+@F2+'='+@Q1+' WHERE '+@T2+'='''+@F1+''''
   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'
/*說明：字段加中括號為了處理字段中含有特殊字符，值得注意得是要實現(xiàn)交叉表的表必須有兩個分類，本例只支持分類字段的數(shù)據(jù)類型是字符型的，最大的問題就是高亮顯示這行的WHERE條件啦，字符類型字段查詢時條件必須加單引號，如果是數(shù)值類型就可以直接寫，所以數(shù)值類型的分類字段更容易實現(xiàn)一些，更可以融合在一個過程中。通常大家看到的交叉表都有行匯總與列匯總等信息，本例就沒有實現(xiàn)，最后一點工作大家自己練練手吧。*/