SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Duplicate rows

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Duplicate rows

    Hello everyone, I need your help.

    I've this mysql table:
    Code:
    +------------+-------------+------+---------------------+------------------+
    | Node       | Description | Zone | XXXXTEL             | Date_Resolution  |
    +------------+-------------+------+---------------------+------------------+
    | KR10240497 | @TERMX      | KR1O | 998482933-980867854 | 2009-10-12       |
    | KR10240497 | @TERMX      | KR1O | 998482933-980867854 | 0000-00-00       |
    | KR10227386 | C.B.S.M.    | KR1M | 999073514-981512863 | 2008-11-13       |
    | KP60203932 | GNRT        | MO6O | T00000352-803521891 | 2012-06-19       |
    | KP60210819 | NAPOLBANKO  | MO6N | T00012472-T00012450 | 2010-04-27       |
    +------------+-------------+------+---------------------+------------------+
    5 rows in set
    I need split value of column 'XXXXTEL' and create and insert in this table new row with the second value splitted in column 'XXXXTEL'.
    I need this output, can you help me?
    Code:
    +------------+-------------+------+---------------------+------------------+
    | Node       | Description | Zone | XXXXTEL             | Date_Resolution  |
    +------------+-------------+------+---------------------+------------------+
    | KR10240497 | @TERMX      | KR1O | 998482933           | 2009-10-12       |
    | KR10240497 | @TERMX      | KR1O | 980867854           | 2009-10-12       |
    | KR10240497 | @TERMX      | KR1O | 998482933           | 0000-00-00       |
    | KR10240497 | @TERMX      | KR1O | 980867854           | 0000-00-00       |
    | KR10227386 | C.B.S.M.    | KR1M | 999073514           | 2008-11-13       |
    | KR10227386 | C.B.S.M.    | KR1M | 981512863           | 2008-11-13       |
    | KP60203932 | GNRT        | MO6O | T00000352           | 2012-06-19       |
    | KP60203932 | GNRT        | MO6O | 803521891           | 2012-06-19       |
    | KP60210819 | NAPOLBANKO  | MO6N | T00012472           | 2010-04-27       |
    | KP60210819 | NAPOLBANKO  | MO6N | T00012450           | 2010-04-27       |
    +------------+-------------+------+---------------------+------------------+

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT node
         , description
         , zone
         , SUBSTRING_INDEX(xxxxtel,'-',1) AS xxxxtel 
         , date_resolution  
      FROM daTable     
    UNION ALL
    SELECT node
         , description
         , zone
         , SUBSTRING_INDEX(xxxxtel,'-',-1)
         , date_resolution  
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT node
         , description
         , zone
         , SUBSTRING_INDEX(xxxxtel,'-',1) AS xxxxtel 
         , date_resolution  
      FROM daTable     
    UNION ALL
    SELECT node
         , description
         , zone
         , SUBSTRING_INDEX(xxxxtel,'-',-1)
         , date_resolution  
      FROM daTable
    thank you.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •