Converting each rows from a table to a Column - 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  |

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|

I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Feb 24 '22 at 12:47
There are, however, countless examples of how to do this in many dialects; what were wrong with those many examples? — Thom A, Feb 24 '22 at 12:48
to add to @Larnu 's comment this is called [pivotting](https://stackoverflow.com/search?q=%5Bsql%5D+pivot) — Raymond Nijland, Feb 24 '22 at 12:49

Kazi Mohammad Ali Nur Romel · Answer 1 · 2022-02-24T13:16:25.413

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:

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

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:

FULLNAME	'Marketing'	'sales'	'service'	'customer'
John Doe	AB.marketing.1240	AB.sales.30	AB.service.2000	AB.customer.11023

db<>fiddle here

Zaynul Abadin Tuhin · Accepted Answer · 2022-02-24T12:51:10.070

0

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

edited Feb 24 '22 at 12:51

answered Feb 24 '22 at 12:49

Zaynul Abadin Tuhin

31,407
5
33
63

3

A `CASE` expression requires an `END`. – Thom A Feb 24 '22 at 12:50

Converting each rows from a table to a Column - SQl

2 Answers2