Mysql Question posted in
The official MySQL documentation can be found here.

MySQL How to Dynamically Select Columns which have at least a value

In MySQL, I have a table which consists of 300+ columns, I have to extract only columns which does have a value at least in one row (not null).

Some answers from other similar question suggested that we can use stored procedure to select only column that is non-nullable. This is not what represent the needs.

The table is handling transactions, which I did not want to manually scan each column and rows, just want to figure out how dynamically we can achieve using SQL (MySQL).

Example using stored procedure but failed to execute:

CREATE PROCEDURE hrdc_ace_dl.find_aps()
begin
    SET @sql = NULL;

SELECT
    GROUP_CONCAT(distinct 
        CONCAT(
            'SELECT ', COLUMN_NAME, ' AS column_name FROM aps_transaction WHERE ', COLUMN_NAME, ' IS NOT NULL LIMIT 1'
        ) SEPARATOR ' UNION ALL ')
INTO @sql
FROM
    information_schema.columns
WHERE
    table_schema = 'hrdc_ace_dl'
    AND table_name = 'aps_transaction';
   
SET @sql = CONCAT('SELECT column_name FROM (', @sql, ') AS tmp_table');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Tags:

2

Answers


  1. 0

    Apart from the usage of LIMIT with UNION ALL functions issue, there’s another part where you have to add to return the COLUMN_NAME as "column name". What I mean is this; below is the query with added parentheses to address the limit+union all issue:

    SELECT
    GROUP_CONCAT(distinct 
        CONCAT(                              /* this part here */
  /*added opening parenthesis --->*/ '(SELECT ', COLUMN_NAME, ' AS column_name 
FROM aps_transaction WHERE ', COLUMN_NAME, ' IS NOT NULL LIMIT 1)'/*<--- added closing parenthesis*/
        ) SEPARATOR ' UNION ALL ') 
INTO @sql
FROM
    information_schema.columns
WHERE
    table_schema = 'hrdc_ace_dl'
    AND table_name = 'aps_transaction';

    The current COLUMN_NAME in the concatenated SELECT will return the 1 which is equivalent of true. So, if you want to return the exact column name, simply wrap it in quotation mark like "', COLUMN_NAME, '".

    Demo fiddle

    Login or Signup to reply.
  2. 0 
    CREATE PROCEDURE hrdc_ace_dl.find_aps()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE col_name VARCHAR(255);
DECLARE cur CURSOR FOR
    SELECT COLUMN_NAME
    FROM information_schema.columns
    WHERE table_schema = 'hrdc_ace_dl'
      AND table_name = 'aps_transaction';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @sql = '';

OPEN cur;

read_loop: LOOP
    FETCH cur INTO col_name;
    IF done THEN
        LEAVE read_loop;
    END IF;

    SET @sql = CONCAT(
        @sql,
        'SELECT ''', col_name, ''' AS column_name FROM aps_transaction WHERE `', col_name, '` IS NOT NULL LIMIT 1 UNION ALL '
    );
END LOOP;

CLOSE cur;

-- Remove the last "UNION ALL"
SET @sql = LEFT(@sql, LENGTH(@sql) - 10);

-- Wrap in a final select
SET @sql = CONCAT('SELECT column_name FROM (', @sql, ') AS tmp_table');

-- Prepare, execute, and clean up
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search