Coalesce equivalent for nth not null value - MySQL

Question

I have been tearing my hair out over this issue. I am working with an existing data set and need to remove all the null values from the columns in table A and shunt them across so they are ordered like in table B

I need something which is equivalent to Coalesce but to retrieve the nth value so I can get the result sorted like in table B

What I have:

Table A

Name CURRENT OCT12 SEPT12 AUG12 JUL12 JUN12  MAY12 APR12
---------------------------------------------------------
A    NULL    NULL  Aug-12 NULL  NULL  Jun-12 NULL  Apr-12
B    Nov-12  NULL  Aug-12 NULL  Jul-12Jun-12 NULL  Apr-12

What I need:

Table B

Name Change1 Change2 Change3 Change4 Change5 Change6
----------------------------------------------------
A    Aug-12  Jun-12  Apr-12  NULL    NULL    NULL   
B    Nov-12  Aug-12  Jul-12  Jun-12  Apr-12  NULL

Code-wise, it would be something like:

Select

first non-null value as Change1  
,second non-null value as Change2  
,third non-null value as Change3  
,fourth non-null value as Change4  
,fifth non-null value as Change5...etc..  

from Table_A

I am using MySQL and i have no idea how to reference the nth non null value in order to call them into Table_B

Does anyone have any ideas?

Perhaps you should show your query as well. It's very difficult to understand what you want. — Ja͢ck, Jan 29 '13 at 08:05
would that mean putting each month on a row rather than in columns? trouble is, each different month in this case comes from a different source table. My issue is probably that im used to querying code held in databases, but i have never had to design a database from scratch so my skills suffer in that respect :( — Shereena, Jan 29 '13 at 08:11
I havent got a query to get the data, thats what i need. I can use Coalesce to pull the the first non-null value from table A into column Change1 in Table B and i can use a convoluted case statement to retrive the second non-null value from table A into table B. My problem comes when trying to retrieve the 3rd, 4th, 5th non null value, i cant think of code to use to extract these from table A into table B... — Shereena, Jan 29 '13 at 08:16

fthiella · Accepted Answer · 2013-01-29T14:27:30.887

I am not sure if I would reccommend using this solution... normalization of your data is always a better choice, but I wanted to answer using plain SQL with some strings functions. This query should return what you are looking for:

SELECT
  Name,
  Changes,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 1)), ',', 1)) as Change1,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 2)), ',', 1)) as Change2,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 3)), ',', 1)) as Change3,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 4)), ',', 1)) as Change4,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 5)), ',', 1)) as Change5,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 6)), ',', 1)) as Change6
FROM (
  SELECT
    Name,
    CONCAT_WS(',', CURRENT, OCT12, SEPT12, AUG12, JUL12, JUN12, MAY12, APR12, ',') as Changes
  FROM
    TableA
) s

I'm concatenating all values in a comma separated string, with two commas at the end of the string (one comma would be enough anyway, but it's easier to put two and just ignore the last one...), and since I'm using CONCAT_WS it will automatically skip null values, and the resulting string will be something like Aug-12,Jun-12,Apr-12,,.

Then in the outer query I'm extracting the n-th element of the string, using SUBSTRIG_INDEX. I would recommend to normalize your database, but if you need a quick fix this solution might be a good starting point.

See it working here.

Please notice that I am not returning NULL values where there are no changes, but I am returning empty strings instead. This can be changed if you need.

This solution worked perfectly, thanks so much for helping! Thanks for the advice RE database design, i will bear it in mind for my own databases in the future :) — Shereena, Jan 29 '13 at 17:08

score -1 · Answer 2 · answered Nov 20 '20 at 04:15

If you don't want to use strings functions you can try this sql using unpivot and row number partitioning:

CREATE TABLE #TableA
  ( 
    "Name" VARCHAR(10),
    "CURRENT" VARCHAR(10),
    OCT12 VARCHAR(10),
    SEPT12 VARCHAR(10),
    AUG12 VARCHAR(10),
    JUL12 VARCHAR(10),
    JUN12 VARCHAR(10),
    MAY12 VARCHAR(10),
    APR12 VARCHAR(10)
  ) 

INSERT INTO #TableA
  ("Name", "CURRENT", OCT12, SEPT12, AUG12, JUL12, JUN12, MAY12, APR12)
VALUES
  ('A', NULL, NULL, 'Aug-12', NULL, NULL, 'Jun-12', NULL, 'Apr-12'),
  ('B', 'Nov-12', NULL, 'Aug-12', NULL, 'Jul-12', 'Jun-12', NULL, 'Apr-12')

SELECT * FROM #TableA;



Select "Name",
    Min(Case row_num When 1 Then data End) Change1,
    Min(Case row_num When 2 Then data End) Change2,
    Min(Case row_num When 3 Then data End) Change3,
    Min(Case row_num When 4 Then data End) Change4,
    Min(Case row_num When 5 Then data End) Change5,
    Min(Case row_num When 6 Then data End) Change6
From
(
    select "Name",data,DBColumnName, 
        ROW_NUMBER() OVER (PARTITION BY "Name" ORDER BY "Name") row_num 
    From #TableA
    unpivot (data for DBColumnName in ("CURRENT",OCT12,SEPT12,AUG12,JUL12,JUN12,MAY12,APR12) ) as z
) TableB
group by "Name";

References:

-- TSQL Pivot without aggregate function

-- https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/

-- https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Coalesce equivalent for nth not null value - MySQL

2 Answers2

Linked