CODEWITHSUNDEEP
sql
user3330703
user3330703

Reputation: 27

Sql query to create following tables

use the below table 1 to generate table 2

Table 1

Col1    A   B   C
------------------
N1      1   0   0 
N2      0   1   0
N3      1   0   0

Table 2
output

new_col
-------
N1 A
N2 B
N3 A

Also how to use Table 2 to generate table 1 above

Upvotes: 0

Views: 81

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

It is unclear what you want when there are multiple "1"s in a row. This is a simple solution:

select id, A from t where A = 1
union all
select id, B from t where B = 1
union all
select id, C from t where C = 1;

In databases that support lateral joins, I would recommend:

select t.id, v.which
from t cross join lateral
     (values ('A', t.A), ('B', B), ('C', C)
     ) v(which, val)
where val = 1;

Upvotes: 0

Patriot
Patriot

Reputation: 312

You can use one case statement. This works in Oracle.

SELECT col1
    || CASE 
        WHEN A = 1
            THEN 'A'
        WHEN B = 1
            THEN 'B'
        WHEN C = 1
            THEN 'C'
        END
NEW_COL
FROM table1

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You can use conditinal with CONCAT() Function :

SELECT CONCAT(col1,' ',
                   CASE WHEN A = 1 THEN 'A' ELSE '' END,
                   CASE WHEN B = 1 THEN 'B' ELSE '' END,
                   CASE WHEN C = 1 THEN 'C' ELSE '' END)
  FROM table1

provided you're on a DBMS with brand name such as MySQL, PostGRES, SQL Server. As an example, Oracle DB won't allow using more than two arguments for CONCAT() Function.

In order to create table2, use for most of the DBMS :

CREATE TABLE table2 AS
SELECT CONCAT(col1,' ',
                   CASE WHEN A = 1 THEN 'A' ELSE '' END,
                   CASE WHEN B = 1 THEN 'B' ELSE '' END,
                   CASE WHEN C = 1 THEN 'C' ELSE '' END) AS new_col
  FROM table1

except for SQL Server in which prefer using :

SELECT CONCAT(col1,' ',
                   CASE WHEN A = 1 THEN 'A' ELSE '' END,
                   CASE WHEN B = 1 THEN 'B' ELSE '' END,
                   CASE WHEN C = 1 THEN 'C' ELSE '' END) AS new_col
  INTO table2                   
  FROM table1

In order to implement a reverse engineering(go back to original table), you need to consider the dialectics for each seperate database to handle string values. Assume you're using MySQL DB, then consider using :

CREATE TABLE table3 AS
SELECT SUBSTRING(new_col,1,instr(new_col,' ')-1) AS col1,
       CASE WHEN instr(new_col,'A') > 0 THEN 1 ELSE 0 END AS A,
       CASE WHEN instr(new_col,'B') > 0 THEN 1 ELSE 0 END AS B,
       CASE WHEN instr(new_col,'C') > 0 THEN 1 ELSE 0 END AS C
  FROM table2

where I used a different table name (table3), since table1 already exists.

Demo

Btw, if Oracle DB is the case, then use :

SELECT CONCAT(CONCAT(col1,' '),
                     CASE WHEN A = 1 THEN 'A' ELSE '' END||
                     CASE WHEN B = 1 THEN 'B' ELSE '' END||
                     CASE WHEN C = 1 THEN 'C' ELSE '' END) AS new_col
  FROM table1

Upvotes: 1

Farhan
Farhan

Reputation: 253

Following SQL query can be help to get requested output:

SELECT Col1,IF(A=1,'A',IF(B=1,'B','C')) AS result FROM `table_name`;

Upvotes: 1

Related Questions