CREATE TABLE TB_TEST(Name VARCHAR(10),Value INT,Date VARCHAR(10),TZTZ CHAR(1))
INSERT INTO TB_TEST SELECT 'A',10 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'B',20 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'A',45 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'B',55 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'A',110,'200101','N'
INSERT INTO TB_TEST SELECT 'B',120,'200101','N'
INSERT INTO TB_TEST SELECT 'A',145,'200102','N'
INSERT INTO TB_TEST SELECT 'B',155,'200102','N'DECLARE @S VARCHAR(8000)
SET @S='SELECT NAME'
SELECT @S=@S+',['+RTRIM(Date)+'(Y)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)'
+',['+RTRIM(Date)+'(N)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END)'
+',变化额度=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN -Value ELSE 0 END)'
+',变化幅度=CAST((SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)-SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END))*1.0/SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END) AS NUMERIC(5,2))'
FROM TB_TEST GROUP BY Date ORDER BY Date
SET @S=@S+' FROM TB_TEST GROUP BY NAME'EXEC(@S)/*
NAME 200101(Y) 200101(N) 变化额度 变化幅度 200102(Y) 200102(N) 变化额度 变化幅度
---------- ----------- ----------- ----------- --------- ----------- ----------- ----------- ---------
A 10 110 10 -10.00 45 145 45 -2.22
B 20 120 20 -5.00 55 155 55 -1.82
*/DROP TABLE TB_TEST
INSERT INTO TB_TEST SELECT 'A',10 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'B',20 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'A',45 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'B',55 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'A',110,'200101','N'
INSERT INTO TB_TEST SELECT 'B',120,'200101','N'
INSERT INTO TB_TEST SELECT 'A',145,'200102','N'
INSERT INTO TB_TEST SELECT 'B',155,'200102','N'DECLARE @S VARCHAR(8000)
SET @S='SELECT NAME'
SELECT @S=@S+',['+RTRIM(Date)+'(Y)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)'
+',['+RTRIM(Date)+'(N)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END)'
+',变化额度=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN -Value ELSE 0 END)'
+',变化幅度=CAST((SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)-SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END))*1.0/SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END) AS NUMERIC(5,2))'
FROM TB_TEST GROUP BY Date ORDER BY Date
SET @S=@S+' FROM TB_TEST GROUP BY NAME'EXEC(@S)/*
NAME 200101(Y) 200101(N) 变化额度 变化幅度 200102(Y) 200102(N) 变化额度 变化幅度
---------- ----------- ----------- ----------- --------- ----------- ----------- ----------- ---------
A 10 110 10 -10.00 45 145 45 -2.22
B 20 120 20 -5.00 55 155 55 -1.82
*/DROP TABLE TB_TEST
INSERT INTO TB_TEST SELECT 'A',10 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'B',20 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'A',45 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'B',55 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'A',110,'200101','N'
INSERT INTO TB_TEST SELECT 'B',120,'200101','N'
INSERT INTO TB_TEST SELECT 'A',145,'200102','N'
INSERT INTO TB_TEST SELECT 'B',155,'200102','N'DECLARE @S VARCHAR(8000)
SET @S='SELECT NAME'
SELECT @S=@S+',['+RTRIM(Date)+'(Y)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)'
+',['+RTRIM(Date)+'(N)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END)'
+',变化额度=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN -Value ELSE 0 END)'
+',变化幅度=CAST(( SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)
-SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END))*1.0/
SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END) AS NUMERIC(5,2))'
FROM TB_TEST GROUP BY Date ORDER BY Date
SET @S=@S+' FROM TB_TEST GROUP BY NAME'EXEC(@S)/*
NAME 200101(Y) 200101(N) 变化额度 变化幅度 200102(Y) 200102(N) 变化额度 变化幅度
---------- ----------- ----------- ----------- --------- ----------- ----------- ----------- ---------
A 10 110 10 -10.00 45 145 45 -2.22
B 20 120 20 -5.00 55 155 55 -1.82
*/DROP TABLE TB_TEST
A 200101(N) 的变化额度是-100
A 200101(N) 的变化幅度是-10
是相对于200101(Y)来说的。B同理,请赐教。
--------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TB_TEST(Name VARCHAR(10),Value INT,Date VARCHAR(10),TZTZ CHAR(1))
INSERT INTO TB_TEST SELECT 'A',10 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'B',20 ,'200101','Y'
INSERT INTO TB_TEST SELECT 'A',45 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'B',55 ,'200102','Y'
INSERT INTO TB_TEST SELECT 'A',110,'200101','N'
INSERT INTO TB_TEST SELECT 'B',120,'200101','N'
INSERT INTO TB_TEST SELECT 'A',145,'200102','N'
INSERT INTO TB_TEST SELECT 'B',155,'200102','N'DECLARE @S VARCHAR(8000)
SET @S='SELECT NAME'
SELECT @S=@S+',['+RTRIM(Date)+'(Y)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)'
+',['+RTRIM(Date)+'(N)]=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END)'
+',变化额度=SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN -Value ELSE 0 END)'
+',变化幅度=CAST((SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END)-SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''N'' THEN Value ELSE 0 END))*1.0/SUM(CASE WHEN Date='''+RTRIM(Date)+''' AND TZTZ=''Y'' THEN Value ELSE 0 END) AS NUMERIC(5,2))'
FROM TB_TEST GROUP BY Date ORDER BY Date
SET @S=@S+' FROM TB_TEST GROUP BY NAME'EXEC(@S)/*
NAME 200101(Y) 200101(N) 变化额度 变化幅度 200102(Y) 200102(N) 变化额度 变化幅度
---------- ----------- ----------- ----------- --------- ----------- ----------- ----------- ---------
A 10 110 -100 -10.00 45 145 -100 -2.22
B 20 120 -100 -5.00 55 155 -100 -1.82
*/DROP TABLE TB_TEST