10 Replies Latest reply on Sep 12, 2017 7:07 PM by Frank Kulash

    sql select query

    2817195

      Hi friends,

       

        I need help with a sql query below to concatenate first_name and last_name..

       

      Source table(example):
      
      create table holder (
      order_no number(22) not null,
      fname varchar2(255),
      lname varchar2(255)
      );
      
      insert into holder values (877,'Mary','Robert');
      insert into holder values (877,'Steve','Lerhaupht');
      insert into holder values (641,'','Sapnar');
      insert into holder values (9611,'Jamie','Scott');
      insert into holder values (877,'Robert','William');
      
      

       

      We would the below result if concatenate the first_name and last_name based on the condition below

       

      select 
      order_no
      ,CASE WHEN (LISTAGG(UPPER(fname), ',') WITHIN GROUP (ORDER BY order_no) IS NOT NULL)
            THEN  LISTAGG(UPPER(fname), ',') WITHIN GROUP (ORDER BY order_no) || ' ' || LISTAGG(UPPER(lname), ',') WITHIN GROUP (ORDER BY order_no)
            ELSE  LISTAGG(UPPER(lname), ',') WITHIN GROUP (ORDER BY order_no)
       END  name
      from holder
      group by order_no;
      
        ORDER_NO NAME
      ---------- ---------------------------------------------
             641 SAPNAR
             877 MARY,ROBERT,STEVE LERHAUPHT,ROBERT,WILLIAM
            9611 JAMIE SCOTT
      
      
      

       

      But, we would need the result to be

        ORDER_NO NAME
      ---------- ---------------------------------------------
             641 SAPNAR
             877 MARY ROBERT,STEVE LERHAUPHT,ROBERT WILLIAM
            9611 JAMIE SCOTT
      
      

       

      Please help.. Thanks a lot

        • 1. Re: sql select query
          Mustafa KALAYCI

          like this?

          select   
          order_no  
          ,CASE WHEN (LISTAGG(UPPER(fname), ',') WITHIN GROUP (ORDER BY order_no) IS NOT NULL)  
                THEN  LISTAGG(UPPER(fname) || ' ' || UPPER(lname), ',') WITHIN GROUP (ORDER BY order_no)
                ELSE  LISTAGG(UPPER(lname), ',') WITHIN GROUP (ORDER BY order_no)  
           END  name  
          from holder  
          group by order_no; 
          
          • 2. Re: sql select query
            mNem

            select

              order_no ,

              listagg( upper(trim (fname || ' ' || lname)), ', ') within group (order by order_no) as name

            from holder

            group by order_no;

            • 3. Re: sql select query
              Frank Kulash

              Hi,

              2817195 wrote:

               

              Hi friends,

               

              I need help with a sql query below to concatenate first_name and last_name..

               

              1. Sourcetable(example):
              2. createtableholder(
              3. ...

              Thanks for posting the sample data; that's very helpful!

               

              But, we would need the result to be

               

               

              1. ORDER_NONAME
              2. -------------------------------------------------------
              3. 641SAPNAR
              4. 877MARYROBERT,STEVELERHAUPHT,ROBERTWILLIAM
              5. 9611JAMIESCOTT

               

              So, you want a delimited list.  You're on the right track: LISTAGG is a good way to get a delimited list with a variable number of items.

              The tricky thing is: What should LISTAGG aggregate?  On rows where fbname is NULL, it should just use lname, but for rows where fname is not NULL, it should use a combination of fname, space and lname.  It sound like the first argument to LISTAGG needs to be a CASE expression.

               

              Here's one way to do that:

              SELECT    order_no

              ,         LISTAGG ( CASE

                                      WHEN  fname IS NOT NULL

                                      THEN  fname || ' '

                                  END

                                  || lname

                                , ','

                                ) WITHIN GROUP (ORDER BY lname, fname)  -- or whatever

                                      AS name_list

              FROM      holder

              GROUP BY  order_no

              ;

              Output:

                ORDER_NO NAME_LIST

              ---------- -----------------------------------------------

                     641 Sapnar

                     877 Steve Lerhaupht,Mary Robert,Robert William

                    9611 Jamie Scott

              If you're GROUPing BY order_no, then it doesn't make much sense for LISTAGG to ORDER BY order_no; that will resut in a tie every time, and the order will be arbitrary.  The query above sorts the delimited items by lname, then fname.  If you really want the order to be arbitrary, then you can ORDER BY NULL.

              1 person found this helpful
              • 4. Re: sql select query
                guru008

                SELECT order_no, Name =

                    STUFF((SELECT ', ' + Fname

                           FROM your_table b

                           WHERE b.order_no= a.order_no

                          FOR XML PATH('')), 1, 2, '')

                FROM your_table a

                GROUP BY order_no

                • 5. Re: sql select query
                  Frank Kulash

                  Hi,

                  2817195 wrote:

                  ...

                  1. select
                  2. order_no
                  3. ,CASEWHEN(LISTAGG(UPPER(fname),',')WITHINGROUP(ORDERBYorder_no)ISNOTNULL)
                  4. THENLISTAGG(UPPER(fname),',')WITHINGROUP(ORDERBYorder_no)||''||LISTAGG(UPPER(lname),',')WITHINGROUP(ORDERBYorder_no)
                  5. ELSELISTAGG(UPPER(lname),',')WITHINGROUP(ORDERBYorder_no)
                  6. ENDname
                  7. fromholder
                  8. groupbyorder_no;

                   

                  ...

                  If some rows have an fname, but other rows with the same order_no have NULL for fname, then the query you posted will ignore the rows with NULL fname in that order_no.  For example, if we add this to the sample data:

                  insert into holder (order_no, lname)  values (877,'Thant'); 

                  then 'Thant' will not appear in the output.  Is that actually what you want?

                  • 6. Re: sql select query
                    2817195

                    Thank you so much Frank. This works

                    • 7. Re: sql select query
                      Frank Kulash

                      Hi,

                      2817195 wrote:

                       

                      Thank you so much Frank. This works

                      Thanks!

                       

                      After seeing the other replies, I actually prefer the one in reply #2, because it's a little simpler.

                      It could be made slightly more efficient by using LTRIM instead of TRIM.

                      • 8. Re: sql select query
                        Mustafa KALAYCI

                        and may I ask how the reply 1 and reply 2 is not working so they are not marked as even helpful? that is rude.

                        • 9. Re: sql select query
                          mNem

                          Frank,

                           

                          Thanks for the thumbs up.

                           

                          It could be made slightly more efficient by using LTRIM instead of TRIM.

                           

                          Used TRIM() to handle cases where fname or lname being empty/null.

                          • 10. Re: sql select query
                            Frank Kulash

                            Hi,

                            mNem wrote:

                            ...

                             

                            Used TRIM() to handle cases where fname or lname being empty/null.

                            Oh, right.  I  wasn't considering the possibility that lname could be NULL.