How can I create column names from a query dynamically?

38
January 13, 2019, at 06:40 AM

I have two tables.

CREATE TABLE `binnat` (
  `binNu` varchar(2) NOT NULL,
  `binNa` varchar(30) NOT NULL,
  `seq` char(2) NOT NULL,
  `se` tinyint(2) unsigned DEFAULT NULL COMMENT 'test sequence\n',
  `W_id` tinyint(2) unsigned NOT NULL,
  `W_lt_id` varchar(15) NOT NULL,
  `W_lt_lpt_id` smallint(4) unsigned NOT NULL,
  PRIMARY KEY (`binNu`,`binNa`,`W_id`,`W_lt_id`,`W_lt_lpt_id`),
  KEY `fk_binNaT_W1_idx` (`W_id`,`W_lt_id`,`W_lt_lpt_id`),
  CONSTRAINT `fk_binNaT_W1` FOREIGN KEY (`W_id`, `W_lt_id`, `W_lt_lpt_id`) REFERENCES `w` (`id`, `lt_id`, `lt_lpt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And

CREATE TABLE `binv` (
  `value` double DEFAULT NULL,
  `binNaT_binNu` varchar(2) NOT NULL,
  `binNaT_binNa` varchar(30) NOT NULL,
  `binNaT_W_id` tinyint(2) unsigned NOT NULL,
  `binNaT_W_lt_id` varchar(15) NOT NULL,
  `binNaT_W_lt_lpt_id` smallint(4) unsigned NOT NULL,
  PRIMARY KEY (`binNaT_binNu`,`binNaT_binNa`,`binNaT_W_id`,`binNaT_W_lt_id`,`binNaT_W_lt_lpt_id`),
  CONSTRAINT `fk_binV_binNaT1` FOREIGN KEY (`binNaT_binNu`, `binNaT_binNa`, `binNaT_W_id`, `binNaT_W_lt_id`, `binNaT_W_lt_lpt_id`) REFERENCES `binnat` (`binnu`, `binna`, `w_id`, `w_lt_id`, `w_lt_lpt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I made a query to retrive results. Here is it.

SELECT binnat.W_id, binnat.binna, binv.value
FROM binnat INNER JOIN binv ON (binnat.W_lt_id = binv.binNaT_W_lt_id) AND (binnat.W_id = binv.binNaT_W_id) AND (binnat.binNa = binv.binNaT_binNa) AND (binnat.binNu = binv.binNaT_binNu)
WHERE (((binv.binNaT_W_lt_id) Like "SI06840"))
GROUP BY binnat.se, binnat_W_id, binv.value, binnat.binnu, binnat.binna

When I execute it, I got this results.

W_id     binNa       value
1         abc          20
2         abc          30
1         def          70
2         def          50

But I found that the result was not what I wanted. The result I wanted was,

W_id     abc     def
1        20       70
2        30       50

I thought I needed codes to create column names dynamically from the query I made. And I found here code that could help me. That is look like,

SET @sql = null;
SELECT GROUP_CONCAT(DISTINCT
            CONCAT('MAX(IF(binnat.W_id = ', 
            W_id, 
            ', binnat.binNa, NULL)) AS `',
            binna,'`'
            )
            ) INTO @sql FROM `***mytable***`;
SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM ***mytable*** 
                  GROUP BY id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But the code use a table in "FROM" phrase rather ther using a query. I'd like to know how I can use a query instead of 'mytable' in the above code.

Thanks.

READ ALSO
Is there a way to fetch string value from MySQL database through Karate

Is there a way to fetch string value from MySQL database through Karate

I am having an issue while karate fetches a string value from MySQL dbreadValue works fine if it is string value it fetches from db but getting error like Unknown column 'SMEMSD22' in 'where clause' How to deal with alphanumeric data with karate

46
Column count doesn't match value count at row 1 in MySQL

Column count doesn't match value count at row 1 in MySQL

I have created a table with the following query:

32
How to mirror mysql database traffic to another server

How to mirror mysql database traffic to another server

I have two server Mysql database in Cent OS 7Server A(192

32
How to calculate time diffrence in mysql

How to calculate time diffrence in mysql

I am storing the user activity in the table by start and end time , now i want to calculate how much overall time a user spends under each subject

30