1 2 Previous Next 25 Replies Latest reply: Aug 19, 2008 9:16 PM by 607227 RSS

    Combine Multiple Rows Into One Column Field

    607227
      Hi everyone.

      I have a data that looks like this.

      ID Name Segment
      001 Angelo A
      001 Angelo D
      001 Angelo G
      002 John A
      002 John B
      002 John K
      002 John P
      003 Chloe Q
      003 Chloe S
      004 Dinah Z

      Can someone help me to build an SQL command that would have the output as follows:

      ID Name SegmentsCombined
      001 Angelo A, D, G
      002 John A, B, K, P
      003 Chloe Q, S
      004 Dinah Z

      basically, you would notice that the segments are combined into one column field.

      I was adviced to use a function, but I am barely starting in PL/SQL and I don't have an idea how to accomplish this.

      Your advice would be highly appreciated! Thanks!
        • 1. Re: Combine Multiple Rows Into One Column Field
          566473
          Oracle 9i - sys_connect_by_path
          Oracle 10g - wm_concat...
          • 2. Re: Combine Multiple Rows Into One Column Field
            560832
            You can accomplish it using pipe lined functions in pl/sql
            http://www.adp-gmbh.ch/ora/plsql/pipeline.html#pivot
            • 3. Re: Combine Multiple Rows Into One Column Field
              607227
              I would appreciate if you could post some actual examples (as I am not really too good in PL/SQL) thanks!
              • 4. Re: Combine Multiple Rows Into One Column Field
                290833
                I would appreciate if you could post some actual
                examples (as I am not really too good in PL/SQL)
                thanks!
                Search this forum for "pivot query" or "string aggregate" and you will find plenty of examples.
                • 5. Re: Combine Multiple Rows Into One Column Field
                  566473
                  1) SYS_CONNECT_BY_PATH
                  with 
                    test as
                      (
                        select '001' as id, 'Angelo' as name, 'A' as segment from dual union all
                        select '001' as id, 'Angelo' as name, 'D' as segment from dual union all
                        select '001' as id, 'Angelo' as name, 'G' as segment from dual union all
                        select '002' as id, 'John' as name, 'A' as segment from dual union all
                        select '002' as id, 'John' as name, 'B' as segment from dual union all
                        select '002' as id, 'John' as name, 'K' as segment from dual union all
                        select '002' as id, 'John' as name, 'P' as segment from dual union all
                        select '003' as id, 'Chloe' as name, 'Q' as segment from dual union all
                        select '003' as id, 'Chloe' as name, 'S' as segment from dual union all
                        select '004' as id, 'Dinah' as name, 'Z' as segment from dual
                      )
                  select id, name,
                         max(ltrim(sys_connect_by_path(segment,','),',')) keep
                             (dense_rank last order by level) as segment_path
                    from (
                           select test.*, row_number() over(partition by id order by segment) as rn
                             from test
                         )
                  start with rn = 1
                  connect by prior id = id
                         and prior rn = rn - 1
                  group by id, name;
                  2) WM_CONCAT
                  with 
                    test as
                      (
                        select '001' as id, 'Angelo' as name, 'A' as segment from dual union all
                        select '001' as id, 'Angelo' as name, 'D' as segment from dual union all
                        select '001' as id, 'Angelo' as name, 'G' as segment from dual union all
                        select '002' as id, 'John' as name, 'A' as segment from dual union all
                        select '002' as id, 'John' as name, 'B' as segment from dual union all
                        select '002' as id, 'John' as name, 'K' as segment from dual union all
                        select '002' as id, 'John' as name, 'P' as segment from dual union all
                        select '003' as id, 'Chloe' as name, 'Q' as segment from dual union all
                        select '003' as id, 'Chloe' as name, 'S' as segment from dual union all
                        select '004' as id, 'Dinah' as name, 'Z' as segment from dual
                      )
                  select id, name, wm_concat(segment,',') segment_path
                    from test
                  group by id, name;
                  • 6. Re: Combine Multiple Rows Into One Column Field
                    607227
                    Hi Sergey,

                    Would I be able to run the sample you posted in TOAD?

                    By the way, the data I posted was for illustrative purpose only. Angelo could appear more than 3 times (as well as with the other Names)

                    If I need to create a function, would you be kind enough to show me some good function that does this pivot thing? thanks!
                    • 7. Re: Combine Multiple Rows Into One Column Field
                      290833
                      I would not recommend using wm_concat in a production system, as it is:
                      - Undocumented
                      - Part of Workspace Manager (which may not be installed).

                      cheers,
                      Anthony
                      • 8. Re: Combine Multiple Rows Into One Column Field
                        607227
                        Hi Anthony,

                        I tried to do the search of the topics you mentioned but the results all came up with advice to search the same topic.

                        I think it would be much better if I could get some real solution on this SQL thing I have.

                        Thanks.
                        • 9. Re: Combine Multiple Rows Into One Column Field
                          337410
                          Hi,

                          Sergey gave you an example already. Example # 1 would work for you.

                          The example would work no matter how many Angelos there are.

                          Please see also http://www.dba-oracle.com/t_with_clause.htm
                          • 10. Re: Combine Multiple Rows Into One Column Field
                            607227
                            Hi BobbyDJ,

                            Thanks for the reply. Pardon me for I am a beginner at this.

                            The one that Sergey posted, I think he specifically state the name 'Angelo' 3x which is the number of data in my sample. Would I be able to execute this in TOAD SQL window right away?

                            Am going to check whether I can make this work.

                            By the way, I got a sample function which I think I can use in this problem as well? but I think am getting some errors in it. Would you mind to take a look?

                            CREATE OR REPLACE FUNCTION SEGMENT_ROUTE(paramTripID IN VARCHAR2) RETURN VARCHAR2 IS p_segment VARCHAR2(2000);
                            BEGIN
                            FOR segment_data IN (
                            SELECT code_locn FROM TRIP_SGMNT WHERE TRIP_ID = paramTripID)
                            LOOP
                            p_segment:= p_segment || code_locn || ', ';
                            END LOOP
                            RETURN p_segment;
                            END SEGMENT_ROUTE;
                            • 11. Re: Combine Multiple Rows Into One Column Field
                              337410
                              Hi,
                              Would I be able to execute this in TOAD SQL window right away?
                              Yes.

                              This part (see below) of the script acts like a table. Here it is named test.
                              You can add many Angelos here and you will see that it will still work.

                              If you are going to work on your table concentrate on the script after the bold part and do away with the with clause.

                              with
                              test as
                              ( .....
                              ....
                              )

                              Select ....

                              For a better explanation on "With Clause" please the link I gave above.
                              • 12. Re: Combine Multiple Rows Into One Column Field
                                607227
                                Hi BobbyDJ,

                                Thanks a lot for the reply. I really am struggling with this, but I think am getting some kind of hang of it.

                                Anyway, I have made some modifications to the SQL command as posted by Sergey. Here's the SQL statement:

                                SELECT TRIP_ID, NAME_STD_CRAFT, DATE_TIME_ETD_SCHD, DATE_TIME_ATD, CODE_FAIL_DEPTR, NO_SGMNT_ID, MAX(LTRIM(sys_connect_by_path(CODE_LOCN,','),',')) KEEP (dense_rank last ORDER BY LEVEL) AS TRIP_SEGMENT_ROUTE
                                FROM ( SELECT TS.TRIP_ID, TR.NAME_STD_CRAFT, TS.DATE_TIME_ETD_SCHD, TS.CODE_LOCN, TS.DATE_TIME_ATD, TS.CODE_FAIL_DEPTR, TS.NO_SGMNT_ID, row_number()
                                     over(PARTITION BY TR.TRIP_ID ORDER BY NO_SGMNT_ID) AS rn
                                     FROM S120CUST.TRIP_SGMNT TS, S120CUST.TRIP_T TR
                                     WHERE TS.DATE_TIME_ATD BETWEEN TO_DATE('02/01/2007', 'dd/mm/yyyy') AND TO_DATE('03/01/2007', 'dd/mm/yyyy')
                                     AND TS.TRIP_ID = TR.TRIP_ID
                                     AND TR.NAME_STD_CRAFT IN ('MS AMAN', 'MS SETIA'))
                                START WITH rn = 1
                                CONNECT BY PRIOR trip_id = trip_id
                                AND PRIOR rn = rn - 1
                                GROUP BY TRIP_ID, NAME_STD_CRAFT, DATE_TIME_ETD_SCHD, DATE_TIME_ATD, CODE_FAIL_DEPTR, NO_SGMNT_ID

                                Now as you may notice, when I run the SQL select, i get the following data: (Delimited text as exported from TOAD)

                                TRIP_ID|NAME_STD_CRAFT|DATE_TIME_ETD_SCHD|DATE_TIME_ATD|CODE_FAIL_DEPTR|NO_SGMNT_ID|CODE_LOCN|TRIP_SEGMENT_ROUTE
                                "M2617"|"MS AMAN"|02/01/2007 06:00:00|02/01/2007 05:45:00||0|"KBP"|"KBP"
                                "M2617"|"MS AMAN"|02/01/2007 06:26:00|02/01/2007 06:19:00||1|"AP9"|"KBP,AP9"
                                "M2617"|"MS AMAN"|02/01/2007 07:20:00|02/01/2007 07:17:00||2|"FA4"|"KBP,AP9,FA4"
                                "M2619"|"MS AMAN"|02/01/2007 15:00:00|02/01/2007 14:27:00||0|"KBP"|"KBP"
                                "M2619"|"MS AMAN"|02/01/2007 16:11:00|02/01/2007 15:44:00||1|"FA4"|"KBP,FA4"
                                "M2619"|"MS AMAN"|02/01/2007 17:00:00|02/01/2007 16:33:00||2|"AP9"|"KBP,FA4,AP9"
                                "M2620"|"MS SETIA"|02/01/2007 06:15:00|02/01/2007 06:27:00|"U"|0|"KBP"|"KBP"
                                "M2620"|"MS SETIA"|02/01/2007 06:46:00|02/01/2007 06:55:00||1|"AP9"|"KBP,AP9"
                                "M2620"|"MS SETIA"|02/01/2007 07:27:00|02/01/2007 07:35:00||2|"BIM/TALI"|"KBP,AP9,BIM/TALI"

                                Basically, the data I need, as from the posted above output, for M2617, it should be:

                                "M2617"|"MS AMAN"|02/01/2007 06:00:00|02/01/2007 05:45:00||0|"KBP,AP9,FA4"

                                for M2619
                                "M2619"|"MS AMAN"|02/01/2007 15:00:00|02/01/2007 14:27:00||0|"KBP,FA4,AP9"

                                for M2620 it should be
                                "M2620"|"MS SETIA"|02/01/2007 06:15:00|02/01/2007 06:27:00||0|"BIM/TALI"|"KBP,AP9,BIM/TALI"

                                as you may notice, the base record should be the one which has NO_SGMNT_ID = 0 and the CODE_LOCN = 'KBP'

                                How do I do this further?

                                Thanks!
                                • 13. Re: Combine Multiple Rows Into One Column Field
                                  636403
                                  can't check it but:
                                  select TRIP_ID,
                                         NAME_STD_CRAFT,
                                         min(DATE_TIME_ETD_SCHD),
                                         min(DATE_TIME_ATD),
                                         CODE_FAIL_DEPTR,
                                         min(NO_SGMNT_ID),
                                         max(LTRIM(SYS_CONNECT_BY_PATH(CODE_LOCN, ','), ',')) KEEP(DENSE_RANK last order by level) as TRIP_SEGMENT_ROUTE
                                  from (select TS.TRIP_ID,
                                               TR.NAME_STD_CRAFT,
                                               TS.DATE_TIME_ETD_SCHD,
                                               TS.CODE_LOCN,
                                               TS.DATE_TIME_ATD,
                                               TS.CODE_FAIL_DEPTR,
                                               TS.NO_SGMNT_ID,
                                               ROW_NUMBER() OVER(partition by TR.TRIP_ID order by NO_SGMNT_ID) as RN
                                        from S120CUST.TRIP_SGMNT TS, S120CUST.TRIP_T TR
                                        where TS.DATE_TIME_ATD between TO_DATE('02/01/2007', 'dd/mm/yyyy') and
                                              TO_DATE('03/01/2007', 'dd/mm/yyyy')
                                        and TS.TRIP_ID = TR.TRIP_ID
                                        and TR.NAME_STD_CRAFT in ('MS AMAN', 'MS SETIA'))
                                  start with RN = 1
                                  connect by prior TRIP_ID = TRIP_ID
                                      and prior RN = RN - 1
                                  group by TRIP_ID, NAME_STD_CRAFT, CODE_FAIL_DEPTR
                                  note you have no grouping for date fields that's why you've got a serveral rows for single TRIP_ID
                                  • 14. Re: Combine Multiple Rows Into One Column Field
                                    337410
                                    Hi,

                                    look at your columns

                                    DATE_TIME_ETD_SCHD, DATE_TIME_ATD ,CODE_FAIL_DEPTR and NO_SGMNT_ID

                                    they make each row unique. So based on your data the result is correct. If you want to have only 1 row each for each trip_id all columns mentioned above should be the same for each trip_id.

                                    Message was edited by:
                                    Bobbydj

                                    Look at qube's solution study it. Look up MIN(). Now there is one more problem there. CODE_FAIL_DEPTR has 1 row = "U" which makes it unique row from its group.
                                    1 2 Previous Next