Question 258826 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is there a SQL query to 'create' columns from data in another column

i need to create 2 additional columns from data in another column in the same table. i have a columns called Role, specifying different roles, e.g. Supervisor, Administrator, etc.
Now i want to create a column for Supervisor and a column for Administrator, where the e.g. ClientName is the same in example attached
expected result

ClientName  Role             Name
A123        Supervisor       Dave 1
A123        Administrator    Dave 2
Q012        Supervisor       Jane2
Q012        Administrator    Kent3

Select *
From contacts

I want to have from the contacts table the following

ClientName       Supervisor          Administrator 
A123             Dave1               Dave2

expected result

Tags:

2

Answers


  1. 0

    Create two new columns Supervisor and Administrator and run two queries, one to update supervisors like

    UPDATE table SET supervisor = name where role='supervisor'

    and second to update admin

     UPDATE table SET admin = name where role ='admin'
    Login or Signup to reply.
  2. 0

    If you want just data retrieval, without table structure change a simple conditional aggregation will do.

    For the given data examples

    CREATE TABLE `contacts` (
  ClientName varchar(10),
  Role varchar(15),
  Name varchar(15)
) ;

insert into contacts values ('A123','Supervisor','Dave1'),
('A123','Administrator','Dave2'), ('Q012','Supervisor','Jane2'),
('Q012','Administrator','Kent3');

    Query

    select ClientName,
       max(case when role='Supervisor' then Name end) as Supervisor,
       max(case when role='Administrator' then Name end) as Administrator
from contacts
group by ClientName;

    Result

    ClientName  Supervisor  Administrator
   A123       Dave1        Dave2
   Q012       Jane2        Kent3

    If you want to change the table structure. Use a single transaction to create a temporary table store the data in the temporary table , then change the structure and insert the data back to main table.

    I have taken in consideration that you are using InnoDB storage engine.

    SET autocommit=0;
LOCK TABLES contacts WRITE;
### Create a temporary table to insert the data from the main table
create temporary table temp_myTable (
  ClientName varchar(10),
  Supervisor varchar(15),
  Administrator varchar(15)
  );

##### Insert unique data to the temporary table
insert into temp_myTable 
 select ClientName,
       max(case when role='Supervisor' then Name end) as Supervisor,
       max(case when role='Administrator' then Name end) as Administrator
from contacts
group by ClientName;

##### Delete all data from main table
truncate contacts;

#####Change  contacts structure 
ALTER TABLE contacts 
  drop column Role,
  drop column Name,
  add column Supervisor varchar(15),
  add column Administrator varchar(15);

###### Insert data to the main table
INSERT INTO contacts  SELECT * FROM temp_myTable;

########### Delete the created temprary table
drop temporary table temp_myTable;

COMMIT;
UNLOCK TABLES;

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search