MySQL Select Dynamic Row Values as Column Names, Another Column as Value

  • Home
  • Languages
  • SQL
  • MySQL Select Dynamic Row Values as Column Names, Another Column as Value

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.

  1. Remove artId from articles - it is completely redundant.
  2. Alter the type of checkdate to date.
  3. 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

Database Design For a Survey

Update Multiple Rows in Same Query Using Postgresql

Copy Tables from One Database to Another in SQL Server

SQL Joins as Venn Diagram

How to Join Multiple SQL Tables Using the Ids

Are There Any Disadvantages to Always Using Nvarchar(Max)

Creating an Index on a Table Variable

Effect of Nolock Hint in Select Statements


Leave a reply



Submit