How to change column values ​​in MySQL?

Question

I have this table:

table1 +------+----------+-----------+----------+ | id | org1 | org2 | org3 | +------+----------+-----------+----------+ | 1 | HR | (NULL) | Staff | +------+----------+-----------+----------+ | 2 | (NULL) | IT | Dev | +------+----------+-----------+----------+ | 3 | (NULL) | (NULL) | Finance | +------+----------+-----------+----------+ 

I want to move all the values ​​to the left so that the final result is:

 table1 +------+----------+-----------+----------+ | id | org1 | org2 | org3 | +------+----------+-----------+----------+ | 1 | HR | Staff | (NULL) | +------+----------+-----------+----------+ | 2 | IT | Dev | (NULL) | +------+----------+-----------+----------+ | 3 | Finance | (NULL) | (NULL) | +------+----------+-----------+----------+ 

Is there an elegant way to do this?

Answer 1

UPDATE:

Based on the answer of cars10, it is necessary to switch the order of COALESCE(org2,org3) and take into account when all 3 columns are NOT NULL

 SELECT id, o1, CASE WHEN o2!=o1 THEN o2 END o2, CASE WHEN o3!=o2 THEN o3 END o3 FROM ( SELECT id ,COALESCE(org1,org2,org3) o1 ,IF((org1 IS NOT NULL) AND (org2 IS NOT NULL) AND (org3 IS NOT NULL), org2, COALESCE(org3,org2) ) o2 ,org3 o3 FROM table1 ) t 

Adding the case mentioned by cars10:

 DROP TABLE IF EXISTS table1; CREATE TABLE `table1` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `org1` VARCHAR(255) DEFAULT NULL, `org2` VARCHAR(255) DEFAULT NULL, `org3` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `table1` VALUES ('1', NULL, 'IT', 'DEV'); INSERT INTO `table1` VALUES ('2', 'HR',NULL,'Staff'); INSERT INTO `table1` VALUES ('3', 'ID','Building',NULL); INSERT INTO `table1` VALUES ('4', 'Support','Business','1st line'); INSERT INTO `table1` VALUES ('5','Finance', NULL, NULL); INSERT INTO `table1` VALUES ('6', NULL, 'Finance', NULL ); INSERT INTO `table1` VALUES ('7', NULL, NULL, 'Finance'); INSERT INTO `table1` VALUES ('8', NULL, NULL, NULL); 

http://www.sqlfiddle.com/#!9/cd969/1

As Thorsten Kettner said, there is no such elegant way to do this. I find above one shortest working solution.

Answer 2

Use coalesce() and subquery

 select id, o1, CASE WHEN o2!=o1 THEN o2 END o2, CASE WHEN o3!=o2 THEN o3 END o3 FROM ( select id, coalesce(org1,org2,org3) o1, coalesce(org2,org3) o2, org3 o3 from tbl ) t 

UPDATE

The previous answer was insufficient, because R2D2 turned out to be completely correct. Unfortunately, you cannot do CTE in mysql, so I created a view instead (I extended the example with another org4 column):

 CREATE VIEW vert AS select id i,1 n, org1 org FROM tbl where org1>'' UNION ALL select id,2, org2 FROM tbl where org2>'' UNION ALL select id,3, org3 FROM tbl where org3>'' UNION ALL select id,4, org4 FROM tbl where org4>''; 

In this view, you can now do the following:

 SELECT id, (select org from vert where i=id order by n limit 1) org1, (select org from vert where i=id order by n limit 1,1) org2, (select org from vert where i=id order by n limit 2,1) org3, (select org from vert where i=id order by n limit 3,1) org4 FROM tbl 

Not pretty, but it does its job, see here: SQLfiddle

input:

 | id | org1 | org2 | org3 | org4 | |----|--------|--------|---------|--------| | 1 | HR | (null) | Staff | IT | | 2 | (null) | IT | Dev | (null) | | 3 | (null) | (null) | Finance | HR | 

output:

 | id | org1 | org2 | org3 | org4 | |----|---------|-------|--------|--------| | 1 | HR | Staff | IT | (null) | | 2 | IT | Dev | (null) | (null) | | 3 | Finance | HR | (null) | (null) | 

Answer 3

A rather elegant solution would be to make the first rows of columns, rank them with an analytic function, and then combine the results:

 select id, max(case when rn = 1 then org end) as org1, max(case when rn = 2 then org end) as org2, max(case when rn = 3 then org end) as org3 from ( select id, org, row_number() over (partition by id order by num) as rn from ( select id, org1 as org, 1 as num from mytable where org1 is not null union all select id, org2 as org, 2 as num from mytable where org2 is not null union all select id, org3 as org, 3 as num from mytable where org3 is not null ) given ) ranked group by id; 

However, MySQL does not support analytic functions. Thus, there is no elegant solution with pure SQL in MySQL. You will need to use CASE WHEN and COALESCE, which is fast with only three columns, but will be quite tedious with a lot.

 select coalesce(org1, org2, org3) as org1, case when org1 is not null then coalesce(org2, org3) else case when org2 is not null then org3 end end as org2 case when org1 is not null and org2 is not null then org3 end as org3 from mytable; 

Another idea would be to write a stored procedure to get the nth nonzero value. This is no longer pure SQL, and I don't know MySQL well enough to find out if this is possible:

 select nth_value(1, org1, org2, org3) as org1, nth_value(2, org1, org2, org3) as org2, nth_value(3, org1, org2, org3) as org3 from mytable;