sql行列转换 | zhouinfo

sql的行列转换 [PIVOT]和[UNPIVOT]
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。

PIVOT

PIVOT函数实现行转列

PIVOT语法格式

SELECT * FROM (需要转换的数据)
PIVOT(<聚合函数>([聚合列值],<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],…[行转列后的列名N]))

<聚合函数>就是我们使用的SUM,COUNT,AVG等Sql聚合函数，也就是行转列后计算列的聚合方式。
[聚合列值]要进行聚合的列名
[行转列前的列名]这个就是需要将行转换为列的列名。
[行转列后的列名]这里需要声明将行的值转换为列后的列名，因为转换后的列名其实就是转换- 前行的值，所以上面格式中的[行转列后的列名1],[行转列后的列名2],[行转列后的列名3],…[行转列后的列名N]其实就是[行转列前的列名]每一行的值。

PIVOT例子

数据

drop table SHOPPING_CART;
CREATE TABLE SHOPPING_CART(
  USER_ID int NOT NULL,
  TYPE_ID INT NOT NULL,
  COUNT decimal(18, 2) NOT NULL,
  PRICE decimal(18, 2) NOT NULL
  );
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,1, '1.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,2, '2.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,2, '3.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (2,1, '4.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (3,2, '4.0','2.5');

数据结构
USER_ID 1 张三 2 李四
TYPE_ID 1 代表食品 2 代表书籍

--将USER_ID分组统计 根据TYPE_ID进行转换列
SELECT * FROM
  (SELECT * FROM SHOPPING_CART)
pivot (sum(PRICE) as PRICE, sum(COUNT) COUNT for TYPE_ID in ('1' AS FOOD, '2' AS BOOK))

查询结果

发现其中PIVOT的USER_ID=1 TYPE=2 的数据合并了

UNPIVOT

UNPIVOT实现列转行

UNPIVOT 语法格式

UNPIVOT([转换为行的列值在转换后对应的列名] for [转换为行的列名在转换后对应的列名] in ([转换为行的列1],[转换为行的列2],[转换为行的列3],…[转换为行的列N]))

[转换为行的列值在转换后对应的列名]在本例中为[(COUNT,PRICE)]
[转换为行的列名在转换后对应的列名]在本例中为[TYPE_ID]
[转换为行的列]这个是声明哪些列要进行列转行,在本例中为((FOOD_COUNT,FOOD_PRICE) as 1, (BOOK_COUNT,BOOK_PRICE) as 2)

UNPIVOT例子

数据完全和 PIVOT例子中查询结构一致

drop table USER_COMMODITY;
CREATE TABLE USER_COMMODITY(
USER_ID int NOT NULL,
FOOD_COUNT decimal(18, 2) NULL,
FOOD_PRICE decimal(18, 2) NULL,
BOOK_COUNT decimal(18, 2) NULL,
BOOK_PRICE decimal(18, 2) NULL
);
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (1,'1','2.5','5','5');
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (2,'4','2.5',NULL,NULL);
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (3,NULL,NULL,'4','2.5');

1
2
3

SELECT * FROM
  (SELECT * FROM USER_COMMODITY)
UNPIVOT((COUNT,PRICE) for TYPE_ID in ((FOOD_COUNT,FOOD_PRICE) as 1, (BOOK_COUNT,BOOK_PRICE) as 2))

查询结构

发现其中PIVOT数据和UNPIVOT执行结果对比

完整的SQL

drop table SHOPPING_CART;
CREATE TABLE SHOPPING_CART(
  USER_ID int NOT NULL,
  TYPE_ID INT NOT NULL,
  COUNT decimal(18, 2) NOT NULL,
  PRICE decimal(18, 2) NOT NULL
  );
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,1, '1.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,2, '2.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (1,2, '3.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (2,1, '4.0','2.5');
INSERT INTO SHOPPING_CART(USER_ID,TYPE_ID,COUNT,PRICE) VALUES (3,2, '4.0','2.5');

SELECT * FROM SHOPPING_CART;
SELECT * FROM
  (SELECT * FROM SHOPPING_CART)
pivot (sum(COUNT) COUNT,sum(PRICE) as PRICE for TYPE_ID in ('1' AS FOOD, '2' AS BOOK));

drop table USER_COMMODITY;
CREATE TABLE USER_COMMODITY(
USER_ID int NOT NULL,
FOOD_COUNT decimal(18, 2) NULL,
FOOD_PRICE decimal(18, 2) NULL,
BOOK_COUNT decimal(18, 2) NULL,
BOOK_PRICE decimal(18, 2) NULL
);
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (1,'1','2.5','5','5');
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (2,'4','2.5',NULL,NULL);
INSERT INTO USER_COMMODITY(USER_ID,FOOD_COUNT,FOOD_PRICE,BOOK_COUNT,BOOK_PRICE) VALUES (3,NULL,NULL,'4','2.5');

SELECT * FROM USER_COMMODITY;
SELECT * FROM
  (SELECT * FROM USER_COMMODITY)
UNPIVOT((COUNT,PRICE) for TYPE_ID in ((FOOD_COUNT,FOOD_PRICE) as 1, (BOOK_COUNT,BOOK_PRICE) as 2))