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

A rather elegant solution would make the columns rows first, rank them with an analytic function and then aggregate 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;

MySQL however doesn't support analytic functions. So there is no elegant solution with pure SQL in MySQL. You will have to use CASE WHEN and COALESCE, which is quickly done with only three columns, but would be quite tiresome with more.

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 non-null value. This is no longer pure SQL and I don't know MySQL well enough to know whether 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;

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.

Answer 3

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) |