-1

I have a table location, which contains the values A1, A2, A3... etc in the id column. I have another table location_color like this:

A1   blue
A1   red
A1   green
A2   yellow
A2   red
A3   blue
A3   red
.
.

Now I want to query so the result would be like this

blue,red,green
yellow,red
Blue, red

My query is 

select location_color.color
from location_color
where location.id = location_color.location_id

but it's not working.

jarlh
  • 42,561
  • 8
  • 45
  • 63
M Mahrous
  • 25
  • 7
  • 1
    In MySQL you can use `GROUP BY` and `GROUP_CONCAT()` – Paul Spiegel Aug 04 '16 at 07:49
  • 3
    Which RDBMS (vendor and version)? The tag *sql* is not enough... – Shnugo Aug 04 '16 at 07:50
  • You did not include the `location` table. But why do you want to have that `where` clause when you are not using anything from the `location` table? Just leave that `where` out. – trincot Aug 04 '16 at 07:53
  • Don't store data as comma separated items (in a column), it will only cause you lots of trouble! (One value per column/row is the SQL way.) – jarlh Aug 04 '16 at 07:54
  • 1
    @jarlh, I think the question shows that the data is already stored that way (one value per row) – trincot Aug 04 '16 at 07:56
  • use `string_agg()` in Postgres –  Aug 04 '16 at 07:56
  • 2
    This was just closed as duplicate for a SQL-Server specific older question. Without spedifying the actual RDBMS. Seems to be to fast, doesn't it? This is - for sure! - a duplicate, but might be bound to the wrong question. And furthermore: The linked question does not handle several leading "IDs", which adds quite an amount of complexity... – Shnugo Aug 04 '16 at 08:03
  • 1
    Tag the dbms used! (The answer will be product specific!!!) – jarlh Aug 04 '16 at 08:42
  • As M Mahrous was back and did not state the actual DBMS I'll vote for closing now... – Shnugo Aug 04 '16 at 11:54

1 Answers1

1

Assuming you're using SQL Server, here is what you can use:

SELECT location_id, stuff((
        select DISTINCT ',' + u.location_color
        from #temp u
        where u.location_id = t.location_id
        for xml path('')
    ),1,1,'') as location_color_csv
FROM #temp t
GROUP BY t.location_id
Chaos Legion
  • 2,730
  • 1
  • 15
  • 14