How to shift 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 shift all values to the left so the end result would be:

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

Is there any elegant way of doing it?

Answer 1

Use coalesce() and a 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 not sufficient, as R2D2 found out quite rightly. Unfortunately you cannot do CTEs in mysql so I created a view instead (I extended the example by another column org4):

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>'';

With this view it is now possible to 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 beautiful, but it gets the job done, 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 2

UPDATE:

Based on the cars10 answer need to switch the order 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 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 mentioned, there is no elegant way of doing this. I'm finding above one the shortest working solution.