MySQL: Split string into rows?

Discussion in 'Programming & Software Development' started by chancey, Jun 3, 2008.

  1. chancey

    chancey Member

    Joined:
    Jan 5, 2005
    Messages:
    1,415
    Location:
    Sydney
    Hey heres a question for the SQL gurus. If I have a result like this:

    Code:
    +---------------------+
    | postcodes           |
    +---------------------+
    | 2001,2020,2030,2035 |
    | 3500,3234,3638      |
    +---------------------+
    Is it possible to make this:

    Code:
    +------+
    | p    |
    +------+
    | 2001 |
    | 2020 |
    ...
    | 3234 |
    | 3638 |
    +------+
    using ONLY a sql statement?
     
  2. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    sure. Create a stored procedure to retrieve all the rows split up the post codes and insert them into another table.
     
  3. Primüs

    Primüs Member

    Joined:
    Apr 1, 2003
    Messages:
    3,428
    Location:
    CFS
    I think it is possible in a SQL command, but i cant quite remember how to do it. Maybe im thinking about something else though. So in that column, the one cell has like 5 different postcodes?
     
  4. KNoodles

    KNoodles Member

    Joined:
    Jun 28, 2002
    Messages:
    180
    Location:
    Melbourne
    Look up PIVOT
     
  5. SkiNLaB

    SkiNLaB Member

    Joined:
    Dec 23, 2001
    Messages:
    709
    Location:
    Sydney's Inner West
    What about a series of SQL statements?
     
  6. OP
    OP
    chancey

    chancey Member

    Joined:
    Jan 5, 2005
    Messages:
    1,415
    Location:
    Sydney
    'PIVOT' doesn't exist in sql ...

    Theres lots of methods for putting multiple pieces of information together (union, concat etc) but nothing I can find that does the opposite to pull things apart...
     
  7. SkiNLaB

    SkiNLaB Member

    Joined:
    Dec 23, 2001
    Messages:
    709
    Location:
    Sydney's Inner West
  8. KNoodles

    KNoodles Member

    Joined:
    Jun 28, 2002
    Messages:
    180
    Location:
    Melbourne
    Sorry, I missed that you were looking in MySQL.

    PIVOT and UNPIVOT do exist in Microsoft TSQL. Link to an example.

    Not sure about MySQL
     

Share This Page

Advertisement: