CODEWITHSUNDEEP
mysqlshell
Adrian Veidt
Adrian Veidt

Reputation: 280

mysql query to select two columns from same table

Below are the sample tables I'm working on

Table 1

╔════╦══════════════╦══════╗
║ KID║  REV         ║ REDO ║
╠════╬══════════════╬══════╣
║  1 ║ 43453453345  ║  2   ║
║  1 ║ 2433423423   ║  1   ║
║  2 ║ 23423423     ║  1   ║
║  2 ║ 5566533      ║  2   ║
╚════╩══════════════╩══════╝

I need to select REV from table 1 using KID and REDO and put in table 2 so that I get a result like below.

Table 2

╔════╦══════════════╦════════════╗ 
║ KID║  REDO1       ║  REDO2     ║
╠════╬══════════════╬════════════║ 
║  1 ║ 43453453345  ║2433423423  ║
║  2 ║ 23423423     ║   5566533  ║ 
╚════╩══════════════╩════════════╝

ie all REV's with REDO=1 must come under REDO1 and REV's with REDO=2 must come under REDO2,all KID is not unique. How to accomplish that with just plain mysql? Please help me

Upvotes: 0

Views: 358

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use the following query to produce the required result set:

SELECT KID, 
       MAX(CASE WHEN REDO = 1 THEN REV) AS REDO1, 
       MAX(CASE WHEN REDO = 2 THEN REV) AS REDO2
FROM Table1
GROUP BY KID

The above uses conditional aggregation in order to place all REV values that are associated with a REDO = 1 value under REDO1 output field, and all REV values that are associated with a REDO = 2 value under REDO2 output field

If you want to insert the result of the above into Table2, you can use a INSERT INTO SELECT statement:

INSERT INTO Table2
SELECT KID, MAX(CASE WHEN REDO = 1 THEN REV END),
       MAX(CASE WHEN REDO = 2 THEN REV END) 
FROM Table1
GROUP BY KID;

Demo here

Upvotes: 2

Related Questions