CREATE
PROCEDURE
UP_TEST(
@T1
VARCHAR
(30),@T2
VARCHAR
(30),
@T3
VARCHAR
(30),@T4
VARCHAR
(30))
AS
BEGIN
DECLARE
@SQL
VARCHAR
(7999),@FIELD
VARCHAR
(30)
SELECT
@SQL=
'SELECT DISTINCT '
+@T3+
' FROM '
+@T1
CREATE
TABLE
#FIELD(FIELD
VARCHAR
(30))
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)+
')'
EXEC
(@SQL)
SELECT
@SQL=
'INSERT INTO CROSS_TEST('
+@T2+
') SELECT DISTINCT '
+@T2+
' FROM '
+@T1
EXEC
(@SQL)
SELECT
@SQL=
'CREATE TABLE TEMP('
+@T2+
' VARCHAR(30),'
+@T3+
' VARCHAR(30),'
+@T4+
' DECIMAL(8,2))'
EXEC
(@SQL)
SELECT
@SQL=
'SELECT '
+@T2+
','
+@T3+
', SUM(QTY) QTY FROM '
+@T1 +
' GROUP BY '
+@T2+
','
+@T3
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 '
+@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'
/*说明:字段加中括号为了处理字段中含有特殊字符,值得注意得是要实现交叉表的表必须有两个分类,本例只支持分类字段的数据类型是字符型的,最大的问题就是高亮显示这行的
WHERE
条件啦,字符类型字段查询时条件必须加单引号,如果是数值类型就可以直接写,所以数值类型的分类字段更容易实现一些,更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息,本例就没有实现,最后一点工作大家自己练练手吧。*/