user979974 user979974 - 1 year ago 82
PHP Question

MySQL Pivot table substring column names

I have a table structure into MySQL databse which looks like this:

Table data:

Ref | Var | 3SAa combined % | 3SAa combined of | 3SAa Forward of |3SAa Forward % | 3SAa reverse % | 3SAa Forward of|
---------------------------------------------------------------------------------------------------------------------
AL23 TT 0.00 38.78 48.39 100.0 12.01 85.3


My desired output is the folowing:

ID | Ref |Var |a combined % | a combined of | a Forward of |a Forward % | a reverse % | a Forward of|
--------------------------------------------------------------------------------------------------------
3SA AL23 TT 0.00 38.78 48.39 100.0 12.01 85.3


I wanted to know if there is any manner to do it with MySQL. The big issue, I have never found any substring function to truncate column names.

Answer Source

you can test my Querys on your DB. You only must setup the first two vars. The Tablename and a Row number that contains the string.

 SET @table_name = 'mytab';
 SET @col_id = 3;

 SELECT DATABASE() INTO @dbname;

 SELECT SUBSTRING_INDEX(COLUMN_NAME,' ' , 1)  INTO @id_name
 FROM information_schema.columns
  WHERE table_schema = @dbname 
   AND TABLE_NAME = @table_name
   AND ORDINAL_POSITION = @col_id;

 SELECT CONCAT('SELECT \'', @id_name, '\' AS Id, ' , GROUP_CONCAT(
     CONCAT( '`',COLUMN_NAME,'`')
     , ' AS `', REPLACE(COLUMN_NAME,CONCAT(@id_name,' ') , '')
     ,'`\n' ), ' FROM ', @table_name) INTO @sql
  FROM information_schema.columns
  WHERE table_schema = @dbname 
   AND TABLE_NAME = @table_name
  ORDER BY ORDINAL_POSITION; 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Test

MariaDB []> SELECT * FROM mytab;
+------+------+-----------------+------------------+
| Ref  | Var  | 3SAa combined % | 3SAa combined of |
+------+------+-----------------+------------------+
| AL23 |      | 0.00            | 38.78            |
+------+------+-----------------+------------------+
1 row in set (0.00 sec)

MariaDB []>  SET @table_name = 'mytab';
Query OK, 0 rows affected (0.00 sec)

MariaDB [bernd]>  SET @col_id = 3;
Query OK, 0 rows affected (0.00 sec)

MariaDB []>  SELECT DATABASE() INTO @dbname;
Query OK, 1 row affected (0.00 sec)

MariaDB []>  SELECT SUBSTRING_INDEX(COLUMN_NAME,' ' , 1)  INTO @id_name
    ->  FROM information_schema.columns
    ->   WHERE table_schema = @dbname
    ->    AND TABLE_NAME = @table_name
    ->    AND ORDINAL_POSITION = @col_id;
Query OK, 1 row affected (0.01 sec)

MariaDB []>  SELECT CONCAT('SELECT \'', @id_name, '\' AS Id, ' , GROUP_CONCAT(
    ->      CONCAT( '`',COLUMN_NAME,'`')
    ->      , ' AS `', REPLACE(COLUMN_NAME,CONCAT(@id_name,' ') , '')
    ->      ,'`\n' ), ' FROM ', @table_name) INTO @sql
    ->   FROM information_schema.columns
    ->   WHERE table_schema = @dbname
    ->    AND TABLE_NAME = @table_name
    ->   ORDER BY ORDINAL_POSITION;
Query OK, 1 row affected (0.01 sec)

MariaDB []> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB []> EXECUTE stmt;
+------+------+------+------------+-------------+
| Id   | Ref  | Var  | combined % | combined of |
+------+------+------+------------+-------------+
| 3SAa | AL23 |      | 0.00       | 38.78       |
+------+------+------+------------+-------------+
1 row in set (0.00 sec)

MariaDB []> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

Please let me know if it works for you