Removing duplicate rows from table and combining data

Question

I'm creating a temp table from multiple sources and need to remove duplicate records from it. For example, my temp table will have data like:

RecordID     Color
1            Black
2            Black
2            Blue

I'd like to consolidate RecordID 2 down to:

RecordID     Color
1            Black
2            Black,Blue

Answer 1

You need to use aggregation. The actual aggregate function depends on your RDBMS.

In MySQL, use GROUP_CONCAT (the default separator is the comma):

SELECT RecordID, GROUP_CONCAT(Color) AS Color
FROM mytable
GROUP BY RecordID

In Oracle, use LISTAGG:

SELECT RecordID, LISTAGG(Color, ',') AS Color
FROM mytable
GROUP BY RecordID

In Postgres (>=9.0) or SQL-Server 2017, use STRING_AGG:

SELECT RecordID, STRING_AGG(Color, ',') AS Color
FROM mytable
GROUP BY RecordID

Answer 2

if its oracle then :

select "RecordID",listagg("Color",',' ) WITHIN GROUP (ORDER BY "RecordID","Color" ) 
Color
FROM Table1 group by "RecordID";

http://sqlfiddle.com/#!4/abaab5/5