Sql Merging multiple records into one record

Question

I have table with two columns user_id and tags.

  user_id    tags
    1    <tag1><tag4>
    1    <tag1><tag2>
    1    <tag3><tag2>
    2    <tag1><tag2>
    2    <tag4><tag5>
    3    <tag4><tag1>
    3    <tag4><tag1>
    4    <tag1><tag2>

I want to merge this two records into one record like this.

  user_id    tags
    1    tag1, tag2, tag3, tag4
    2    tags, tag2, tag4, tag5
    3    tag4, tag1
    4    tag1, tag2

How can i get this? Can anyone help me out. Also need to convert tags field into array []. I don't have much knowledge on typical sql commads. I just know the basics. I am a ruby on rails guy.

For this to achieve you have to write **SP** or **Function** with **Query**. — Romesh, May 21 '13 at 11:31
can you knowledge me with the more info on this. I need to sql only for this time. — Ashwin Yaprala, May 21 '13 at 11:32

score 1 · Accepted Answer · edited May 21 '13 at 11:47

1

You should look into the GROUP_CONCAT function in mysql.A good example is here

In your case it would be something like:

SELECT user_id, GROUP_CONCAT(tags) FROM tablename GROUP BY user_id

edited May 21 '13 at 11:47

Prahalad Gaggar

11,389
16
53
71

answered May 21 '13 at 11:47

Cargo23

3,064
16
25

1

Its not something you can do readily in SQL, as you are talking about pulling apart data within a field. If you had originally limited the tags field to just 1 tag, you would be able to do it. – Cargo23 May 21 '13 at 18:40
@ashwinkumar to get unique/sorted tags, you have to use a sub-select and then do the GROUP_CONCAT against just the unique row.s – Cargo23 Jan 23 '17 at 16:35

score 1 · Answer 2 · edited May 23 '17 at 12:01

1

duplicate of https://stackoverflow.com/questions/16218616/sql-marching-values-in-column-a-with-more-than-1-value-in-column-b/16218678#16218678

select user_id, group_concat(tags separator ',')
from t
group by user_id

edited May 23 '17 at 12:01

Community

1
1

answered May 21 '13 at 11:49

ulkas

5,748
5
33
47

score 0 · Answer 3 · answered May 21 '13 at 11:56

Try this one -:

SELECT t2.userid,
stuff((
SELECT ', ' + t1.tags
FROM table_name as t1
where t1.userid = t2.userid
FOR XML PATH('')
), 1, 2, '')
FROM table_name as t2
GROUP BY t2.userid

I hope this helps you out.

Check the example -: http://www.sqlfiddle.com/#!3/85c89/6

Sql Merging multiple records into one record

3 Answers3

Linked

Related