Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Answered: working with strings inside of string_agg

    This is my first post to dbforums. Please let me know if there is a better sub-forum that I should post my question. And please let me know if my question needs further clarification.
    I am working with BIRT report designer 4.6.0 and pulling data from a postgreSQL database. I am using pgadmin with postgres 9.3.

    An example of the table in postgreSQL that I am pulling from looks like this.

    syn_id record_id genus_synonym specific_epithet_synonym
    1 1 Acer rubrum
    2 1 Acer spicatum
    3 1 Acer campestre
    4 2 Betula lenta
    5 2 Betula alleghaniensis
    6 2 Carya ovalis


    I am concatenated the genus_synonym and specific_epithet_synonym fields and then aggregated the ones with the same record_id.

    The code I am using looks like this

    Code:
    Select
    
    string_agg(CONCAT(CONCAT(s."genus_synonym"), ' ', s.specific_epithet_synonym), ', '
    
    Order by 
                  s."genus_synonym" ASC,
                  s."specific_epithet_synonym" ASC) as syno
    
    FROM
                 "public"."synonyms" as s
    I am outputting my results by record_id.

    So for record_id 1 the result looks like
    Acer campestre, Acer rubrum, Acer spicatum
    and
    for record_id 2 the result looks like
    Betula alleghaniensis, Betula lenta, Carya ovalis


    What I would like to do is to abbreviate the genus to a single letter with a period following it for all but the first time the same genus appears for a particular record_id. So what I would like to get back would look like this:

    for record_id 1
    Acer campestre, A. rubrum, A. spicatum
    and
    for record_id 2
    Betula alleghaniensis, B. lenta, Carya ovalis

    Does anyone have any thoughts on how I can modify my query or do something else to get these results?

    Thanks

  2. Best Answer
    Posted by shammat

    "you need to first create a number that you can use to check if it's the first occurrence of the synonym or a subsequent one. This can be done using a window function:

    Code:
    select record_id, 
           row_number() over (partition by record_id order by genus_synonym, specific_epithet_synonym) as rn, 
           case row_number() over (partition by record_id order by genus_synonym)
              when 1 then concat(genus_synonym, ' ', specific_epithet_synonym)
              else concat(left(genus_synonym,1), '. ', specific_epithet_synonym)
           end as syn
    from synonyms
    The above returns:
    Code:
    record_id | rn | syn                  
    ----------+----+----------------------
            1 |  1 | Acer campestre       
            1 |  2 | A. rubrum            
            1 |  3 | A. spicatum          
            2 |  1 | Betula alleghaniensis
            2 |  2 | B. lenta             
            2 |  3 | C. ovalis
    Now this can be used to do the aggregation:
    Code:
    select record_id, 
           string_agg(syn, ', ' order by rn) as syno
    from (
      select record_id, 
             row_number() over (partition by record_id order by genus_synonym, specific_epithet_synonym) as rn, 
             case row_number() over (partition by record_id order by genus_synonym)
                when 1 then concat(genus_synonym, ' ', specific_epithet_synonym)
                else concat(left(genus_synonym,1), '. ', specific_epithet_synonym)
             end as syn
      from synonyms
    ) t
    group by record_id
    This then returns:
    Code:
    record_id | syno                                      
    ----------+-------------------------------------------
            1 | Acer campestre, A. rubrum, A. spicatum    
            2 | Betula alleghaniensis, B. lenta, C. ovalis
    Note that you do not need to nest concat() calls. You can pass multiple parameters to it. If one of the values might be empty (or null), it's better to use concat_ws() instead"


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    you need to first create a number that you can use to check if it's the first occurrence of the synonym or a subsequent one. This can be done using a window function:

    Code:
    select record_id, 
           row_number() over (partition by record_id order by genus_synonym, specific_epithet_synonym) as rn, 
           case row_number() over (partition by record_id order by genus_synonym)
              when 1 then concat(genus_synonym, ' ', specific_epithet_synonym)
              else concat(left(genus_synonym,1), '. ', specific_epithet_synonym)
           end as syn
    from synonyms
    The above returns:
    Code:
    record_id | rn | syn                  
    ----------+----+----------------------
            1 |  1 | Acer campestre       
            1 |  2 | A. rubrum            
            1 |  3 | A. spicatum          
            2 |  1 | Betula alleghaniensis
            2 |  2 | B. lenta             
            2 |  3 | C. ovalis
    Now this can be used to do the aggregation:
    Code:
    select record_id, 
           string_agg(syn, ', ' order by rn) as syno
    from (
      select record_id, 
             row_number() over (partition by record_id order by genus_synonym, specific_epithet_synonym) as rn, 
             case row_number() over (partition by record_id order by genus_synonym)
                when 1 then concat(genus_synonym, ' ', specific_epithet_synonym)
                else concat(left(genus_synonym,1), '. ', specific_epithet_synonym)
             end as syn
      from synonyms
    ) t
    group by record_id
    This then returns:
    Code:
    record_id | syno                                      
    ----------+-------------------------------------------
            1 | Acer campestre, A. rubrum, A. spicatum    
            2 | Betula alleghaniensis, B. lenta, C. ovalis
    Note that you do not need to nest concat() calls. You can pass multiple parameters to it. If one of the values might be empty (or null), it's better to use concat_ws() instead
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jan 2017
    Posts
    2
    Perfect Shammat! This is what I was looking for. You answer helped me understand how to accomplish this on the example I gave and now on my larger and more complex query. So thank you.

    One thing though, I was trying to partition not just on the record_id but secondarily on the genus_synonym field. I learned I could do this by adding a comma and then that second field.

    This is what the modified query looks like:


    Code:
    select record_id, 
           string_agg(syn, ', ' order by genus_synonym, specific_epithet_synonym ) as syno
    from (
      select record_id, genus_synonym,specific_epithet_synonym,
             row_number() over (partition by record_id, genus_synonym order by genus_synonym, specific_epithet_synonym) as rn, 
             case row_number() over (partition by record_id, genus_synonym order by genus_synonym, specific_epithet_synonym)
                when 1 then concat(genus_synonym, ' ', specific_epithet_synonym)
                else concat(left(genus_synonym,1), '. ', specific_epithet_synonym)
             end as syn
      from synonyms
    ) t
    group by record_id

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
  •