I don't know how to split one column to two separated with diffrent condition.

If contact is type == 'E' would like save AS email, otherwise type == '6' save AS phone.

Please for help!

CodePudding user response：

You want CASE expression

select *, 
   case when type = 'E' then contact end email,
   case when type = '6' then contact end phone
from KONTAKTI

CodePudding user response：

If you want to add those columns to the table there are two options to achieve this.

First create a copy of kontakti with the desired changes. Please note that I had to assume your datatypes.

create table kontakti_temp (
    ac_subject varchar(8),
    type char(1),
    phone varchar(16),
    email varchar(256)
);

Then insert the data from kontakti into kontakti_temp and split the contact fields by using case.

insert into kontakti_temp
select ac_subject
      , type
      , case when type = '6' then contact end as phone
      , case when type = 'E' then contact end as mail
from kontakti;

Then rename both tables accordingly

Or you could use a create table as statement

create table kontakti_temp as
select ac_subject
      , type
      , case when type = '6' then contact end as phone
      , case when type = 'E' then contact end as mail
from kontakti;

The datatypes of the columns in kontakti_temp will be derived from the ones returned by the above select statement.

CodePudding user response：

Select:

select ac_subject,
   max(case when type = 'E' then contact end) email,
   max(case when type = '6' then contact end) phone
from kontakti
group by ac_subject
;

Test DDL:

create table kontakti (
    ac_subject varchar(8),
    type char(1),
    contact varchar(256)
);
  
INSERT INTO kontakti
    (`ac_subject`, `type`, `contact`)
VALUES
    ('a1', 'E', '[email protected]'),
    ('a1', '6', '41895478'),
    ('b2', 'E', '[email protected]'),
    ('v5', '6', '243243'),
    ('v5', 'E', '[email protected]')
;

create table kontakti_temp as
select ac_subject,
   max(case when type = 'E' then contact end) email,
   max(case when type = '6' then contact end) phone
from kontakti
group by ac_subject
;

Output:

CodePudding user response：

It's not okey. I need result like this: