行转列与列转行

来源：互联网发布：数据库考试试题及答案编辑：程序博客网时间：2024/05/21 12:49

有群友询问sql写法，要求实现功能如下

示例数据

DROP TABLE t/create table t (a varchar2(30),b varchar2(30),c varchar2(30),d varchar2(30) );insert into t values('门店1','品牌1','2','8');insert into t values('门店1','品牌2','3','6');insert into t values('门店1','品牌3','2','10');insert into t values('门店2','品牌1','1','4');insert into t values('门店2','品牌2','4','8');insert into t values('门店2','品牌3','4','20');insert into t values('门店3','品牌1','3','12');insert into t values('门店3','品牌2','2','4');insert into t values('门店3','品牌3','1','5');/

sql实现方法如下

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as test@192.168.1.118:1521/orclSQL> SQL> WITH T1 AS  2   (SELECT GROUPING(T.B) AS GP_B,  3           T.B AS 品牌,  4           SUM(CASE T.A  5                 WHEN '门店1' THEN  6                  T.C  7               END) AS 销量_门店1,  8           SUM(CASE T.A  9                 WHEN '门店2' THEN 10                  T.C 11               END) AS 销量_门店2, 12           SUM(CASE T.A 13                 WHEN '门店3' THEN 14                  T.C 15               END) AS 销量_门店3, 16           SUM(T.C) AS 销量_合计, 17           SUM(CASE T.A 18                 WHEN '门店1' THEN 19                  T.D 20               END) AS 收入_门店1, 21           SUM(CASE T.A 22                 WHEN '门店2' THEN 23                  T.D 24               END) AS 收入_门店2, 25           SUM(CASE T.A 26                 WHEN '门店3' THEN 27                  T.D 28               END) AS 收入_门店3, 29           SUM(T.D) AS 收入_合计 30      FROM T 31     GROUP BY ROLLUP(T.B) 32     ORDER BY 1 DESC,2) 33  SELECT CASE 34           WHEN GP_B = 1 THEN 35            '销量合计' 36           ELSE 37            品牌 38         END AS 品牌, 39         销量_门店1 AS 门店1, 40         销量_门店2 AS 门店2, 41         销量_门店3 AS 门店3, 42         销量_合计 AS 合计 43    FROM T1 44  UNION ALL 45  SELECT CASE 46           WHEN GP_B = 1 THEN 47            '收入合计' 48           ELSE 49            品牌 50         END AS 品牌, 51         收入_门店1 AS 门店1, 52         收入_门店2 AS 门店2, 53         收入_门店3, 54         收入_合计 AS 合计 55    FROM T1 56  UNION ALL 57  SELECT CASE 58           WHEN GP_B = 1 THEN 59            '收入合计' 60           ELSE 61            品牌 62         END AS 品牌, 63         round(销量_门店1 * 收入_门店1 / 销量_合计,2) AS 门店1, 64         round(销量_门店2 * 收入_门店1 / 销量_合计,2) AS 门店2, 65         round(销量_门店3 * 收入_门店1 / 销量_合计,2) AS 门店3, 66         收入_合计 AS 合计 67    FROM T1;品牌              门店1        门店2        门店3         合计---------- ---------- ---------- ---------- ----------销量合计             7          9          6         22品牌1               2          1          3          6品牌2               3          4          2          9品牌3               2          4          1          7收入合计            24         32         21         77品牌1               8          4         12         24品牌2               6          8          4         18品牌3              10         20          5         35收入合计          7.64       9.82       6.55         77品牌1            2.67       1.33          4         24品牌2               2       2.67       1.33         18品牌3            2.86       5.71       1.43         3512 rows selected