Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2017
    Posts
    12

    Unanswered: How to make duplicate sub string as a new columns using DB2 sql

    Hello everyone

    My scenario is that I have one column

    col1
    |abc_project1_10|

    |pqr_project1_20|

    |pqr_project2_30|

    |xyz_project2_50|

    |xyz_project2_60|

    output will be as
    name project1 project2
    |abc| |10 | | null |

    |pqr| |20 | | null |

    |pqr| | null | | 30 |

    |xyz| | null | | 110 |(110 is the sum of duplicate sub string of project 2)

    how Can I do it using DB2 SQL,please give me a any suggestion
    Thanks in Advance

    Regards
    Amruta
    Last edited by amrutaraut; 10-16-17 at 03:56.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    303
    Provided Answers: 45
    Hi,

    Try this:
    Code:
    select 
      name
    , sum(case project when 'project1' then num end) project1
    , sum(case project when 'project2' then num end) project2
    from (
    select 
      col1
    , xmlcast(xmlquery('tokenize($s, "_")[1]' passing t.col1 as "s") as varchar(20)) name
    , xmlcast(xmlquery('tokenize($s, "_")[2]' passing t.col1 as "s") as varchar(20)) project
    , xmlcast(xmlquery('tokenize($s, "_")[3]' passing t.col1 as "s") as int) num
    from table (values
      'abc_project1_10'
    , 'pqr_project1_20'
    , 'pqr_project2_30'
    , 'xyz_project2_50'
    , 'xyz_project2_60'
    ) t (col1)
    )
    group by name
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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