MySql converting row to column

Question

I have a MySQL query which provides the following result,

The query used is

SELECT  modi, aid FROM (SELECT 
 GROUP_CONCAT(id) his,
an_id aid, 
GROUP_CONCAT(m_id) modi
FROM 
ah 
GROUP BY an_id, m_id) t1
GROUP BY aid, modi
order by aid;

Now I need a MySQLquery which will give me a result set where if columnaidhas the same value in two rows should create a new column with sameaidandmodi`.

For example, the first two rows in the image as same aid but different modi. The result I need should have 3 column and the row should look like,

aid           modi                  modi2
27     6,6,6,6,6,6,6,6,6              8

I am very new to SQL. Thanks in advance

MySQL is not the same as Oracle.. Besides the how to ask (stackoverflow.com/help/how-to-ask) guidelines requires you provide reproducible data a image isn't reproducible data — Raymond Nijland
– Raymond Nijland, Commented Oct 30, 2018 at 16:20

Gordon Linoff · Accepted Answer · 2018-10-30 16:20:46Z

1

Well, one method is:

SELECT aid, min(modi) as modi1, max(modi) as modi2
FROM (SELECT an_id as aid, 
             GROUP_CONCAT(m_id) as modi
      FROM annotation_history ah
      GROUP BY an_id
    ) t1
GROUP BY aid
HAVING min(modi) <> max(modi);

answered Oct 30, 2018 at 16:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySql converting row to column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related