Mysql select dynamic row values as column name from a table and value from another table
Finally the code is working
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(a.ques_id = ',
ques_id,
', a.ques_ans, NULL)) AS `',
ques_name,'`'
)
) INTO @sql
FROM survey_answer inner join survey_question on survey_answer.ques_id=survey_question.id;
set @sql = CONCAT('select u.user_name ,q.category_id,a.p_code, ' ,@sql,' FROM `survey_answer` as a
LEFT JOIN `users` as u ON a.user_id = u.user_id
LEFT JOIN `survey_question` as q ON a.ques_id= q.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Mysql dynamic row values as column names
I think you want this query
SELECT SQL_NO_CACHE
n.number, n.name,
MAX(CASE WHEN f1.field_name='age' THEN nf1.value END) as Age,
MAX(CASE WHEN f1.field_name='email' THEN nf1.value END) as Email,
MAX(CASE WHEN f1.field_name='gender' THEN nf1.value END) as Gender
FROM
number AS n
LEFT JOIN
number_field AS nf1 ON n.id = nf1.number_id
RIGHT JOIN
field AS f1 ON f1.id = nf1.field_id
WHERE
1 = 1
GROUP BY n.number,n.name
ORDER BY number
FIDDLE
Dynamic
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN f1.field_name= ''',
f1.field_name,
''' THEN nf1.`value` END) AS ',
f1.field_name
)
)INTO @sql
FROM
number_field AS nf1
RIGHT JOIN
field AS f1 ON f1.id = nf1.field_id
ORDER BY f1.field_name;
SET @sql=CONCAT('SELECT n.number, n.name, ',@sql,' FROM
number AS n
LEFT JOIN
number_field AS nf1 ON n.id = nf1.number_id
RIGHT JOIN
field AS f1 ON f1.id = nf1.field_id
GROUP BY n.number,n.name
ORDER BY number');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FIDDLE
MySQL Use dynamic row values as column names
Step #1: Get the column names:
select distinct BoxName from t
For example, this query will return:
BoxName
-----------
Small Box
Medium Box
Large Box
Regular Box
Jumbo Box
Step #2: Assemble a dynamic query. Now that you know the columns you can prepare the main query as:
select
Route,
max(case when BoxName = 'Small Box' then price end) as `Small Box`,
max(case when BoxName = 'Medium Box' then price end) as `Medium Box`,
max(case when BoxName = 'Large Box' then price end) as `Large Box`,
max(case when BoxName = 'Regular Box' then price end) as `Regular Box`,
max(case when BoxName = 'Jumbo Box' then price end) as `Jumbo Box`
from t
group by Route
order by max(display_order)
MySql select dynamic row values as column names
As from reference question's approach of using group_concat
you can do so,but note one thing as your job ids increases per exec_id
group then group_concat
approach will not be optimal due to its default length of 1024 characters to concatenate and for your dynamic columns this will surely cross that limit,but this limit can be increased as mentioned in documentation
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN job_id = ''',
job_id,
''' THEN start_time END) `',
job_id,
'_start`',
',',
'MAX(CASE WHEN job_id = ''',
job_id,
''' THEN end_time END) `',
job_id,
'_end`'
)
)
INTO @sql
FROM t;
SET @sql = CONCAT('SELECT exec_id, ', @sql, '
FROM t
GROUP BY exec_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Fiddle Demo
mysql select dynamic row values as column names, another column as value
Unlike some other RDBMS MySQL doesn't have native support for pivoting operations of this sort by design (the developers feel it's more suited to the presentation, rather than database, layer of your application).
If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around with CASE
, I'd probably just use MySQL's GROUP_CONCAT()
function:
SELECT CONCAT(
'SELECT `table`.id', GROUP_CONCAT('
, `t_', REPLACE(name, '`', '``'), '`.value
AS `', REPLACE(name, '`', '``'), '`'
SEPARATOR ''),
' FROM `table` ', GROUP_CONCAT('
LEFT JOIN `table` AS `t_', REPLACE(name, '`', '``'), '`
ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
SEPARATOR ''),
' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;
PREPARE stmt FROM @qry;
EXECUTE stmt;
See it on sqlfiddle.
Note that the result of GROUP_CONCAT()
is limited by the group_concat_max_len
variable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long name
values).
mysql select dynamic rows as column names
If the number of skill IDs are known and fixed, then you can use a simple pivot query:
SELECT Student_Id,
MAX(CASE WHEN Skill_id = 30 THEN skill-Score END) AS `30`,
MAX(CASE WHEN Skill_id = 50 THEN skill-Score END) AS `50`,
MAX(CASE WHEN Skill_id = 63 THEN skill-Score END) AS `63`,
MAX(CASE WHEN Skill_id = 42 THEN skill-Score END) AS `42`
FROM r_job_scores
GROUP BY Student_Id
If you want to get a total of student scores, one way to do that would be to subquery the query I gave above and compute the tally:
SELECT t.Student_Id,
t.`30` + t.`50` + t.`63` + t.`42` AS overall_score
FROM
(
SELECT Student_Id,
MAX(CASE WHEN Skill_id = 30 THEN skill-Score END) AS `30`,
MAX(CASE WHEN Skill_id = 50 THEN skill-Score END) AS `50`,
MAX(CASE WHEN Skill_id = 63 THEN skill-Score END) AS `63`,
MAX(CASE WHEN Skill_id = 42 THEN skill-Score END) AS `42`
FROM r_job_scores
GROUP BY Student_Id
) t
Select rows with dynamic names from different table as column name in new table matched by articleId
Well, you should have put some more effort trying to adapt one of the solutions you've found for your case, or if you do not understand something there - try harder, ask for the specific things that you do not understand - how to join two tables, how to concatenate strings, how to use variables in MySQL, etc., learn how the things work.
That said, let's take solution #6
for instance - Mysql query to dynamically convert rows to columns, and adapt it for your case.
SET @sql = NULL;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'MAX(IF(CAST(checkdate AS date) = ''', CAST(checkdate AS date), ''', h.price, NULL)) AS ', '`', CAST(checkdate AS date), '`'
)
ORDER BY checkdate
)
INTO
@sql
FROM
priceHistory;
SET @sql = CONCAT('
SELECT
a.id AS articleId,
a.price AS currentPrice,
', @sql, '
FROM
articles a LEFT JOIN priceHistory h
ON
a.id = h.artId
GROUP
BY a.id
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
Et voilà!
+-----------+--------------+------------+------------+------------+--------------------+
| articleId | currentPrice | 2014-11-07 | 2014-11-08 | 2014-11-09 | 2014-11-10 |
+-----------+--------------+------------+------------+------------+--------------------+
| 1 | 5 | NULL | 2.5 | 8.5 | 5 |
| 2 | 2 | NULL | NULL | 2.5 | 2 |
| 3 | 3 | NULL | NULL | NULL | 3 |
| 4 | 9.5 | 7 | 8 | 10 | 9.5 |
| 5 | 1.3 | NULL | NULL | 2 | 1.2999999523162842 |
+-----------+--------------+------------+------------+------------+--------------------+
There are three things to fix in your data structure.
- Remove
artId
fromarticles
- it is completely redundant. - Alter the type of
checkdate
todate
. - Never use
float
for storing money (take a look at the value 1.2999999523162842), use NUMERIC instead!
MySQL Query with dynamic column name
Pseudo-code:
SELECT DISTINCT ChannelNumber
FROM source
while ($row = fetch_assoc()) {
$channel_col = 'ch'.$row['ChannelNumber']
$sql = "
UPDATE destination
JOIN source ON StartTime = destination.minutes
AND source.ChannelNumber = $row[ChannelNumber]
SET destination.$channel_col = 1
";
query($sql);
}
Normally you have to be concerned about dynamically creating a string which becomes an SQL query to avoid SQL injection, but since all the data is coming from within your database it shouldn't be a problem.
Related Topics
How to Create Multiple One to One's
MySQL Unknown Column in on Clause
How to Use Any Instead of in in a Where Clause
Insert into Multiple Tables in One Query
SQL Server 2005 Pivot on Unknown Number of Columns
Does Count(*) Always Return a Result
How to Split a Single Column Values to Multiple Column Values
Why Is SQL Server Losing a Millisecond
T-SQL - Insert Data into Parent and Child Tables
Can a Foreign Key Be Null And/Or Duplicate
Update Multiple Rows in Same Query Using Postgresql
Copy Tables from One Database to Another in SQL Server
How to Join Multiple SQL Tables Using the Ids
Are There Any Disadvantages to Always Using Nvarchar(Max)