select table and replace column fields by value from multiple tables in mysql

Question

I am not more familiar with MySQL.i have been working in mssql. currently, we have a requirement to select table column fields replaced by values from multiple tables. I tried out with some queries but not getting the proper result since it has some inner options. please refer to the fiddle. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=15c6f66c363ab16a3437a3911c266bde

Please advise me on this

Thanks in advance

EDIT: The code (and text) from dbfille:

1:

CREATE TABLE TableData_400
 (id INT,
 created_date DATETIME,
 sample_1 TEXT,
 sample_2 TEXT,
 sample_3 TEXT,
 sample_4_1 TINYINT,
 sample_4_2 TINYINT,
 sample_4_3 TINYINT 
 );
INSERT INTO TableData_400 VALUES
('1','2019-05-17 06:19:13','aaa', 'bbb','ccc',0,0,1);
select * from TableData_400

2:

DROP table TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id INT,
 sample_title TEXT 
 );

3:

INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4','checkbox');
select * from TableData_elements where Dataid=400

4:

CREATE TABLE TableData_elements_options
 (Dataid INT,
 sample_id INT,
 option_id INT,
 optionss TEXT 
 );
 INSERT INTO TableData_elements_options VALUES
('400',4,1,'First'),
('400',4,2,'second'),
('400',4,3,'Third');
select * from TableData_elements_options where Dataid=400

expected result like

select datas from TableData_400 like the following way ,

 id created_date fname Sname Lname first second third 
 1 2019年05月17日 06:19:13 aaa bbb ccc 0 0 1

5:

SELECT CONCAT( 'SELECT ', GROUP_CONCAT(CONCAT('sample_', sample_id, ' ', sample_title)),
 ' FROM TableData_400'
 )
INTO @sql FROM TableData_elements;
SELECT @sql; 
PREPARE myStatement FROM @sql;
EXECUTE myStatement ;
DROP PREPARE myStatement;

Answer 1

You could change sample_id, like below:

DROP TABLE TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id CHAR(10),
 sample_title TEXT 
 );
INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4_1','first'),
('400','4_2','second'),
('400','4_3','third');

OR you could change query at the end:

SELECT 
 CONCAT( 'SELECT ', 
 GROUP_CONCAT(CONCAT('sample_', 
 te.sample_id, 
 CASE WHEN teo.option_id>0 THEN 
 CONCAT('_',teo.option_id) 
 ELSE '' END, 
 ' ', 
 sample_title)),
 ' FROM TableData_400' ) 
FROM TableData_elements te 
LEFT JOIN TableData_elements_options teo ON 
 teo.Dataid=te.Dataid and 
 teo.sample_id=te.sample_id ;

EDIT: (also tak not of optionss):

SELECT 
 CONCAT( 'SELECT ', 
 GROUP_CONCAT(CONCAT('sample_', 
 te.sample_id, 
 CASE WHEN teo.option_id>0 THEN 
 CONCAT('_',teo.option_id) 
 ELSE '' END, 
 ' ', 
 CASE WHEN teo.option_id>0 THEN
 teo.optionss
 ELSE sample_title END)),
 ' FROM TableData_400' ) 
FROM TableData_elements te 
LEFT JOIN TableData_elements_options teo ON 
 teo.Dataid=te.Dataid and 
 teo.sample_id=te.sample_id ;

• Muhasseena T U: thankyou for your reply. i tried this query but i have return the result for option field is option for all 3 options, we can only take the fields dynamically so we can not do any alias manualy.
• Muhasseena T U: and we can not do any changes to table fields, because it's dynamically generated. need to take options from TableData_elements_options table dynamically.
• Muhasseena T U: Thank you for your reply. I want to do some more customization on this (append single quotes for field names). dbfiddle.uk/…