1 2 Previous Next 19 Replies Latest reply: Apr 24, 2013 3:12 AM by Manik RSS

    Neeed Help

    976410
      Hi Friends,

      I have one table having data like this

      ID Name Sal
      1 ABC 200
      2 XYZ 250
      3 MNP 300
      3 BCD 350
      3 JKL 400

      Now i want one query which will display data like below

      ID Name Sal
      1 ABC 200
      2 XYZ 250
      3[MNP] MNP 300
      3[BCD] BCD 350
      3[JKL] JKL 400

      In above example ID=3 have multiple values and i want to concate ID with Name only if ID have multiple values.

      Please help me building query for this.

      Thanks in Advance
      Anand
        • 1. Re: Neeed Help
          ranit B
          And that'll need your 'ID' column to be a VARCHAR column. Is that fine?
          Currently, what datatype is your 'ID' column?
          with xx as
          (
              select '1' id, 'ABC' name, 200 sal from dual UNION ALL
              select '2' id, 'XYZ' name, 250 sal from dual UNION ALL
              select '3' id, 'MNP' name, 300 sal from dual UNION ALL
              select '3' id, 'BCD' name, 350 sal from dual UNION ALL
              select '3' id, 'JKL' name, 400 sal from dual 
          )
          select 
              DECODE(count(1) OVER(partition by id), 1, id, id||'['||name||']'),
              name,
              sal 
          from xx;
          Output :
          1     ABC     200
          2     XYZ     250
          3[BCD]     BCD     350
          3[JKL]     JKL     400
          3[MNP]     MNP     300
          Hope this helps,

          Edited by: ranit B on Apr 24, 2013 11:57 AM
          -- added Query.
          • 2. Re: Neeed Help
            976410
            Yes, ID column is VARCHAR only.
            • 3. Re: Neeed Help
              Purvesh K
              One way of doing it:
              with data as
              (
                select 1 userid, 'abc' ename, 200 sal from dual union all
                select 2 userid, 'def' ename, 300 sal from dual union all
                select 3 userid, 'ghi' ename, 400 sal from dual union all
                select 3 userid, 'jkl' ename, 500 sal from dual union all
                select 3 userid, 'mno' ename, 600 sal from dual
              )
              select case when cnt > 1 then d.userid || '[' || ename || ']' else to_char(d.userid) end userid, ename, sal
                from data d,
                     (
                      select userid, count(*) cnt
                        from data
                       group by userid
                     ) a
               where d.userid = a.userid;
              
              USERID                                        ENAME SAL                    
              --------------------------------------------- ----- ---------------------- 
              1                                             abc   200                    
              2                                             def   300                    
              3[ghi]                                        ghi   400                    
              3[jkl]                                        jkl   500                    
              3[mno]                                        mno   600
              Using Analytics:
              with data as
              (
                select 1 userid, 'abc' ename, 200 sal from dual union all
                select 2 userid, 'def' ename, 300 sal from dual union all
                select 3 userid, 'ghi' ename, 400 sal from dual union all
                select 3 userid, 'jkl' ename, 500 sal from dual union all
                select 3 userid, 'mno' ename, 600 sal from dual
              )
              select case when count(*) over (partition by userid order by userid) > 1 then d.userid || '[' || ename || ']' else to_char(d.userid) end userid, ename, sal
                from data d;
              
              USERID                                        ENAME SAL                    
              --------------------------------------------- ----- ---------------------- 
              1                                             abc   200                    
              2                                             def   300                    
              3[jkl]                                        jkl   500                    
              3[mno]                                        mno   600                    
              3[ghi]                                        ghi   400
              Use of reserved keywords viz. ID, NAME is a bad practice. You should replace it with some meaningful names.
              • 4. Re: Neeed Help
                Manik
                May be this: without sub query:

                WITH t AS
                        (SELECT 1 id, 'ABC' nm, 200 sal FROM DUAL
                         UNION ALL
                         SELECT 2, 'XYZ', 250 FROM DUAL
                         UNION ALL
                         SELECT 3, 'MNP', 300 FROM DUAL
                         UNION ALL
                         SELECT 3, 'BCD', 350 FROM DUAL
                         UNION ALL
                         SELECT 3, 'JKL', 400 FROM DUAL)
                SELECT 
                       CASE
                          WHEN COUNT (*) OVER (PARTITION BY id) > 1 THEN
                             id||' [' || nm || ']'
                          ELSE
                             to_char(id)
                       END
                         id,
                         nm,
                       sal
                  FROM t;
                Output:
                ID     NM     SAL
                ----------------------------------
                1     ABC     200
                2     XYZ     250
                3 [BCD]     BCD     350
                3 [JKL]     JKL     400
                3 [MNP]     MNP     300
                Cheers,
                Manik.
                • 5. Re: Neeed Help
                  Manik
                  New Guru ;)

                  Cheers,
                  Manik.
                  • 6. Re: Neeed Help
                    976410
                    Thanks Manik and Purvesh for your reply.

                    But problem is, i have millions of records in that table and i can't use this method for the same.

                    Please help me with other method.

                    Thanks
                    • 7. Re: Neeed Help
                      ranit B
                      Check my last post. Query added.

                      Avoid using Reserved Keywords like 'Id' and 'Name' for naming self-created objects.
                      Check this for more :
                      select * 
                      from v$reserved_words;
                      • 8. Re: Neeed Help
                        Purvesh K
                        973407 wrote:
                        Thanks Manik and Purvesh for your reply.

                        But problem is, i have millions of records in that table and i can't use this method for the same.

                        Please help me with other method.

                        Thanks
                        Why can't you use this for Millions of records?

                        Did you even try before discarding the solution? If yes, then post the explain plan and the execution time.
                        • 9. Re: Neeed Help
                          ranit B
                          Hi Purvesh,

                          Congrats for promotion to 'Guru'. Great job!
                          count(*) over (partition by userid order by userid)
                          You've used an <tt>'ORDER BY'</tt> clause. Is that actually required when doing COUNT(*) ??
                          count(*) over (partition by userid)
                          Removing it also works fine (as we just need the COUNT of 'ID' whenever there are duplicates) and we can actually avoid an extra Sort operation.

                          Please rectify me if i'm getting it wrong.
                          • 10. Re: Neeed Help
                            976410
                            Thanks Friends and Appreciate your valuable replies.

                            Its working fine.

                            Anand
                            • 11. Re: Neeed Help
                              ranit B
                              Hi Anand,

                              Can you please check the performance and post the plans of all the queries posted by me, Purvesh and Manik?

                              Just for my own reference, want to check which query behaves properly when provided with huge data.
                              • 12. Re: Neeed Help
                                Purvesh K
                                ranit B wrote:
                                Hi Purvesh,

                                Congrats for promotion to 'Guru'. Great job!
                                Thank you, but status isn't more important here. :)
                                count(*) over (partition by userid order by userid)
                                You've used an <tt>'ORDER BY'</tt> clause. Is that actually required when doing COUNT(*) ??
                                count(*) over (partition by userid)
                                Removing it also works fine (as we just need the COUNT of 'ID' whenever there are duplicates) and we can actually avoid an extra Sort operation.

                                Please rectify me if i'm getting it wrong.
                                You are correct. Its not required. Habituated towards use of Order by in Analytics, I did not think towards it. Thanks for bringing it to notice.
                                • 13. Re: Neeed Help
                                  Manik
                                  You are correct. Its not required. Habituated towards use of Order by in Analytics, I did not think towards it. Thanks for bringing it to notice.
                                  IMO does not make any difference its always window sort in plan when you use analytics..
                                  Correct me if this is false.

                                  Cheers,
                                  Manik.
                                  • 14. Re: Neeed Help
                                    976410
                                    Right now i don't have much data into that table. But sharing you explain plan for both.

                                    For Purvesh query

                                    PLAN_TABLE_OUTPUT
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                    Plan hash value: 1920302219

                                    -------------------------------------------------------------------------------------
                                    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                                    -------------------------------------------------------------------------------------
                                    | 0 | SELECT STATEMENT | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    | 1 | SORT ORDER BY | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    | 2 | WINDOW SORT | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    |* 3 | TABLE ACCESS FULL| SAPT_BBS_ROLE | 38 | 78318 | 2 (0)| 00:00:01 |
                                    -------------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                    3 - filter("STATUS"='ACTIVE' AND "BBS_ROLE"<>'FBX-READ-ONLY' AND
                                    "VALID_FROM_DATE"<=SYSDATE@! AND "VALID_UNTIL_DATE">=SYSDATE@!)

                                    Note
                                    -----
                                    - dynamic sampling used for this statement

                                    20 rows selected.


                                    For Ranit Query.


                                    PLAN_TABLE_OUTPUT
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                    Plan hash value: 1920302219

                                    -------------------------------------------------------------------------------------
                                    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                                    -------------------------------------------------------------------------------------
                                    | 0 | SELECT STATEMENT | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    | 1 | SORT ORDER BY | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    | 2 | WINDOW SORT | | 38 | 78318 | 4 (50)| 00:00:01 |
                                    |* 3 | TABLE ACCESS FULL| SAPT_BBS_ROLE | 38 | 78318 | 2 (0)| 00:00:01 |
                                    -------------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                    3 - filter("STATUS"='ACTIVE' AND "BBS_ROLE"<>'FBX-READ-ONLY' AND
                                    "VALID_FROM_DATE"<=SYSDATE@! AND "VALID_UNTIL_DATE">=SYSDATE@!)

                                    Note
                                    -----
                                    - dynamic sampling used for this statement

                                    20 rows selected.


                                    Thanks
                                    1 2 Previous Next