7 Replies Latest reply: Feb 6, 2013 3:04 AM by nazzu RSS

    Help Request - Row value to Clumn

    nazzu
      Hi,

      I build the sql which displays the output as follows

      select msi.segment1 ITM_NUM,
      msi.inventory_item_status_code ITM_NAME,
      mc.concatenated_segments IMT_TYPE
      FROM MTL_ITEM_CATEGORIES MIC,
      MTL_CATEGORY_SETS_TL MCST,
      MTL_CATEGORY_SETS_B MCS,
      MFG_LOOKUPS ML,
      MTL_CATEGORIES_B_KFV MC,
      mtl_system_items_b msi,
      gl_code_combinations gcc,
      fnd_flex_values_tl fvt
      WHERE MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
      AND MCS.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
      AND MCST.LANGUAGE = USERENV ('LANG')
      AND MIC.CATEGORY_ID = MC.CATEGORY_ID
      AND MCS.CONTROL_LEVEL = ML.LOOKUP_CODE
      AND ML.LOOKUP_TYPE = 'ITEM_CONTROL_LEVEL_GUI'
      and msi.inventory_item_id=mic.inventory_item_id
      and msi.segment1 like 'Zaheer'
      and mcst.category_set_name in ('Offering Type','Service Category')
      and msi.expense_account=gcc.code_combination_id
      and gcc.segment5=fvt.flex_value_meaning
      and fvt.language='US'


      ITM NO   ITMNAME TYPE
      1          Zaheer     P
      1     zaheer     C

      But I would like to display the records as follows

      ITM_NO ITM_NAME TYPE1 TYPE2
      1     Zaheer          P          C

      How to build the query for this.
      Please help me on this.

      Thanks,
      Naz.

      Edited by: nazzu on Feb 5, 2013 5:15 AM

      Edited by: nazzu on Feb 5, 2013 5:17 AM
        • 1. Re: Help Request - Row value to Clumn
          BluShadow
          Forum FAQ:

          {message:id=9360005}

          and while you're there make sure you read:

          {message:id=9360002}
          • 2. Re: Help Request - Row value to Clumn
            AlbertoFaenza
            Hi Naz,

            BluShadow already pointed you to the correct FAQ.

            Remember when you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            For what is regarding your case, not having your sample data I have created something similar.
            It's important to know if you are using 11g as PIVOT function is available from that version on.
            
            Assuming that your type might be different (not only C and P) and you have a maximum of 2 types you can do something similar to the query below:
            WITH mydata(itm_no, itm_name, itm_type) AS
            (
            SELECT 1, 'Zaheer', 'P' FROM DUAL UNION ALL
            SELECT 1, 'Zaheer', 'C' FROM DUAL UNION ALL
            SELECT 2, 'Test', 'D' FROM DUAL UNION ALL
            SELECT 2, 'Test', 'E' FROM DUAL
            )
            , mydata2 AS
            (
            SELECT itm_no, itm_name, itm_type
            , row_number() OVER (PARTITION BY itm_no, itm_name ORDER BY itm_type) rn
            FROM mydata
            )
            SELECT itm_no, itm_name, itm_type1, itm_type2
            FROM mydata2
            PIVOT (MIN(itm_type) FOR rn IN ( 1 as itm_type1
            , 2 AS itm_type2)
            );


            ITM_NO ITM_NAME ITM_TYPE1 ITM_TYPE2
            ---------- -------- --------- ---------
            1 Zaheer C P
            2 Test D E
            Regards.
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: Help Request - Row value to Clumn
              nazzu
              Hi Blu, Al ..

              Thanks for your help.
              But when i execute the with query, it is erroring for me 'ORA-32033: unsupported column aliasing'.
              Is this because of the version which i am using.
              I am using the database version of

              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

              and collect the above version from the sql
              select * from v$version;

              Any other alternate way, please give me the hint.

              Thanks again for your help.

              Naz.
              • 4. Re: Help Request - Row value to Clumn
                Rahul_India
                Pivot wont work in 10g
                You need to use CASE statement
                • 5. Re: Help Request - Row value to Clumn
                  jeneesh
                  WITH clause also is not supported in 10g as you use. You may try MAX(DECODE(.. technique, which is available in the FAQ link already pointed out..
                  select itm_no, itm_name,
                         max(decode(rn,1,itm_type)) typ1,
                         max(decode(rn,2,itm_type)) typ2
                  from 
                    (
                      SELECT itm_no, itm_name, itm_type,
                              row_number() OVER (PARTITION BY itm_no, itm_name ORDER BY itm_type) rn 
                      FROM mydata
                    )
                  group by itm_no, itm_name;
                  • 6. Re: Help Request - Row value to Clumn
                    BluShadow
                    jeneesh wrote:
                    WITH clause also is not supported in 10g as you use.
                    Or to put it correctly...

                    The WITH clause (known as subquery factoring) is supported in 10g, but the solution provided is using recursive subquery factoring (identified by the fact the WITH clause has parameters in brackets), and that is a new feature from 11gR2.
                    • 7. Re: Help Request - Row value to Clumn
                      nazzu
                      Hi Experts,

                      Thanks for all your support.
                      I fixed the issue with your support.