mysql Select values from multiple different columns values into single column(PIVOT)

Question

+---------+------+--------+------+
|   year   | id  | band  | score | 
+---------+------+--------+------+
|    1990  |  1  | a     |  10  |
|    1991  | 1   | b     |  20  |
|    1992  | 1   | c     |  30  |
|    1993  | 1   | d     |  40  |
|    1994  | 1   | e     |  40  |
+---------+------+--------+------+

I want output like below

+-------+-------+-------+-------+------+-----
| id    | 1990  | 1991  | 1992  |1993  | 1994 |
+-------+-------+-------+-------+------+------
|  1    |  10(a) | 20(b) | 30(c)| 40(d)| 50(e)|
-----------------------------------------------

MySQL <> SQL Server <> Postgres <> SQLite! Pleas tag only the one database you are running. — GMB
– GMB, Commented Oct 9, 2020 at 18:01

tuhin47 · Accepted Answer · 2020-10-09 20:07:01Z

1

You can make the statement as follows:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ''',
      year,
      ''' then concat(score,''','(''',',band,',''')',''') end) year_',
      year
    )
  ) INTO @sql
FROM
  mytable;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM mytable 
                   GROUP BY id');
select @sql;


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

Here is the working fiddle

answered Oct 9, 2020 at 20:07

tuhin47

6,2735 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-10-09 18:03:25Z

0

Use conditional aggregation. I think that it makes more sense to put the bands and scores in separate columns, so:

select id,
    max(case when year = 1990 then score end) score_1990,
    max(case when year = 1990 then band  end) band_1990,
    max(case when year = 1991 then score end) score_1991,
    max(case when year = 1991 then band  end) band_1991,
    ...
from mytable
group by id

answered Oct 9, 2020 at 18:03

GMB

224k25 gold badges103 silver badges151 bronze badges

2 Comments

sakthi. nagulsamy Over a year ago

Hey , thanks for your reply .What if we have 50k ids having band and score values for the last 50 years.

GMB Over a year ago

@sakthi.nagulsamy: you need one (actually, two) expressions per year, that's how conditional aggregation works. Otherwise, you need dynamic SQL, which is much more complicated.

Collectives™ on Stack Overflow

mysql Select values from multiple different columns values into single column(PIVOT)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related