| 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  |

I have a table in above format with name and department details. I am looking for a way convert all the rows under department to a separate columns so that the table will look like the below table. For each fullName, I am expecting to extract marketing, sales,service etc in a single row as below.

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

CodePudding user response：

If you are using sql server then you can use PIVOT

Schema and insert statements:

 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');

Query:

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

Output:

db<>fiddle here

If you are using oracle then:

Query:

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

Output:

db<>fiddle here

CodePudding user response：

try like below

    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