Sql dividing one record into to many records

Question

I have table t1 sql records

user_id    tags
  1     <tag1><tag2>
  1     <tag1><tag3>
  2     <tag2><tag3>

I want to divide this records into multiple records and do distinct on it. I want to know how to divide the records based on tags <> into another table t2

I need ouput like this in table t2

user_id    tags
  1     <tag1> 
  1     <tag2>
  1     <tag1>
  1     <tag3>
  2     <tag2>
  2     <tag3>

Can u guys give me some sql query to do??

Not one columns, Separate them into new multiple records with its user_id into another table t2 — Ashwin Yaprala, May 22 '13 at 06:51
It's better to use table like `t2` (pairs of `user`-`tag`), than keep all user tags in one field. Redesign your tables if you can. — Heavy, May 22 '13 at 06:56
Its not problem if it is done in table t1 or need to create new table t2 — Ashwin Yaprala, May 22 '13 at 06:56
Actually I am not good student in sql, I am not able to write proper query for it — Ashwin Yaprala, May 22 '13 at 06:58
check this question: http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows — Heavy, May 22 '13 at 07:00

Stephan · Accepted Answer · 2013-05-24T22:38:59.417

0

Try this:

SELECT
    user_id,
    SUBSTRING_INDEX(tags,'<',2) as tag
FROM
    t1
UNION ALL
SELECT
    user_id,
    SUBSTRING_INDEX(tags,'>',-2) as tag
FROM
    t1

UPDATE: for distinct values you can use :

SELECT
    user_id,
    tag
FROM (
    SELECT
        user_id,
        SUBSTRING_INDEX(tags,'<',2) as tag
    FROM
        t1
    UNION ALL
    SELECT
        user_id,
        SUBSTRING_INDEX(tags,'>',-2) as tag
    FROM
        t1
) as tmp
    GROUP BY
        user_id,
        tag

edited May 24 '13 at 22:38

answered May 22 '13 at 07:44

Stephan

8,000
3
36
42

1

Don't forget to use `SELECT DISTINCT` to remove duplicates if necessary – May 24 '13 at 07:19
given the example of desired output its not needed to remove duplicates, that is why i didn't use `DISTINCT` – Stephan May 24 '13 at 12:35
OP's question says "I want to divide this records into multiple records and do distinct on it." – May 24 '13 at 15:14
indeed your right but his output isn't distinct , but i will update the query – Stephan May 24 '13 at 22:34

Sql dividing one record into to many records

1 Answers1