将表中的每一行转换为列-SQl

Question

| Fullname  | department |code               |
| --------  | -----------|-------------------|
| John Doe  | Marketing  |AB.marketing.1240  | 
| John Doe  | sales      |AB.sales.30        |
| John Doe  | service    |AB.service.2000    |
| John Doe  | customer   |AB.customer.11023  |

我有一张上面格式的表格，上面有姓名和部门的详细信息。我正在寻找一种方法，将department下的所有行转换为单独的列，使表看起来像下表。对于每个全名，我希望在一行中提取营销、销售、服务等，如下所示。

| Fullname  | Marketing         |sales              |service         |customer |
| --------  | ----------------- |-------------------|----------------|-----------|
| John Doe  | AB.marketing.1240 |AB.sales.30        |AB.service.2000 | AB.customer.11023|

Answer 1

如果您使用的是sql server，那么您可以使用PIVOT

模式和插入语句：

CREATE TABLE DEPARTMENTS(Fullname VARCHAR(50), department VARCHAR(50), code VARCHAR(50));
INSERT INTO DEPARTMENTS VALUES('John Doe','Marketing','AB.marketing.1240');
INSERT INTO DEPARTMENTS VALUES('John Doe','sales','AB.sales.30');
INSERT INTO DEPARTMENTS VALUES('John Doe','service','AB.service.2000');
INSERT INTO DEPARTMENTS VALUES('John Doe','customer','AB.customer.11023');

查询：

select *
from DEPARTMENTS
pivot
(
max(code) for department in ([Marketing],[sales],[service],[customer])
) as pvt

输出：

全名
John Doe

尝试下面的

select Fullname,
max(case when department='Marketing' then code end) as Marketing,
max(case when department='sales' then code end)  as sales,
max(case when department='service' then code end) as service,
max(case when department='customer' then code end) as customer
from table_name group by Fullname