This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Sql Redundant Rows Manipulation

Hi all,

I have a table with two columns. Column 2 has some redundancies which I know how to filter but when doing it I would like to merge the text from column 1 and leave only single row. (Ideally separete the text by ";"):

Col1 Col2

1 AA 2 BB 3 AA

so that I get:

Col1 Col2

1;3 AA 2 BB

Is it possible? Thanks for help

Tom

• 1,764 views • link
updated by Dave Richardson Dave Richardson • written by Tom • Tom

1 answer

The solution depends on the type of database you are using. If you are using a mysql database this should work:

select 
    group_concat(col_1 separator ';'),
    col_2 
from the_table
group by col_2;

For other databases you will need to look up the correct function to use in place of group_concat.

• 0 views • link
written by Dave Richardson Dave Richardson

Group_concat was the thing I was looking for, thanks.

• 0 views • link
written by Tom • Tom

Log in to answer this question.

More posts like this