6 Replies Latest reply: Jan 22, 2014 5:48 AM by AnnPricks E RSS

    how to make pivot table in query ?

    913349

      Hi all,

      SELECT COUNT(C.entity_id) as Entity_Id,

        e.description       AS region,

        d.business_initials AS business_group

      FROM

        (SELECT a.entity_id ,

          a.region,

          B.business_group

        FROM ms_fcr_entity_reg a,

          ms_fcr_entity_bus b

        WHERE a.entity_id         = b.entity_id

        AND B.business_group NOT IN ('Citi Commercial Bank - (CCB)','Global Consumer Group - (GCG)')

        ) C,

        ms_fcr_business_group_data d,

        ms_fcr_managed_geography e

      WHERE C.region       = e.managed_geography_id

      AND C.business_group = d.business_group_id

      group by E.DESCRIPTION,

        d.business_initials;

      my out put is like this

      entity_id region  businessgroup

      190        NAM    CCB

      40           NAM    COR

      106          NAM    CPB

      189          NAM    GCG

      168           NAM    ICG

      40             NAM    MLS

       

       

      but i need output like this

      REGION    BG(CCB)  BG(COR)  BG(CPB) BG(GCG) BG(ICG) BG(MLS)

      NAM           190             40              106          189           168          40

       

      Thanks

      Damby

        • 1. Re: how to make pivot table in query ?
          Hoek

          See the SQL and PL/SQL FAQ for examples:

          Re: 4. How do I convert rows to columns?

           

          Just add another query on top of your existing one, using the MAX(DECODE()) approach, if you have a fixed number of columns....

          • 2. Re: how to make pivot table in query ?
            Partha Sarathy S

            Try this,

             

            WITH T1 AS
            (SELECT COUNT(C.entity_id) as Entity_Id,

              e.description       AS region,

              d.business_initials AS business_group

            FROM

              (SELECT a.entity_id ,

                a.region,

                B.business_group

              FROM ms_fcr_entity_reg a,

                ms_fcr_entity_bus b

              WHERE a.entity_id         = b.entity_id

              AND B.business_group NOT IN ('Citi Commercial Bank - (CCB)','Global Consumer Group - (GCG)')

              ) C,

              ms_fcr_business_group_data d,

              ms_fcr_managed_geography e

            WHERE C.region       = e.managed_geography_id

            AND C.business_group = d.business_group_id

            group by E.DESCRIPTION,

              d.business_initials
            )
            SELECT * FROM T1
            PIVOT (MAX(entity_id) AS BG FOR (BUSINESSGROUP) IN ('CCB','COR','CPB','GCG','ICG','MLS'))
            ;

            • 3. Re: how to make pivot table in query ?
              AnnPricks E

              Try the below

              SELECT region,

                            MAX(DECODE( business_group,'CCB',Entity_id)) "BG(CCB)",

                            MAX(DECODE( business_group,'COR',Entity_id)) "BG(COR)",

                            MAX(DECODE( business_group,'CPB',Entity_id)) "BG(CPB)",

                            MAX(DECODE( business_group,'GCG',Entity_id)) "BG(GCG)",

                            MAX(DECODE( business_group,'ICG',Entity_id)) "BG(ICG)",

                            MAX(DECODE( business_group,'MLS',Entity_id)) "BG(MLS)"

              FROM (SELECT COUNT(C.entity_id) as Entity_Id,

                e.description       AS region,

                d.business_initials AS business_group

              FROM

                (SELECT a.entity_id ,

                  a.region,

                  B.business_group

                FROM ms_fcr_entity_reg a,

                  ms_fcr_entity_bus b

                WHERE a.entity_id         = b.entity_id

                AND B.business_group NOT IN ('Citi Commercial Bank - (CCB)','Global Consumer Group - (GCG)')

                ) C,

                ms_fcr_business_group_data d,

                ms_fcr_managed_geography e

              WHERE C.region       = e.managed_geography_id

              AND C.business_group = d.business_group_id

              group by E.DESCRIPTION,

                d.business_initials) GROUP BY region;
              • 4. Re: how to make pivot table in query ?
                913349

                Thanks a lot  dear..

                • 5. Re: how to make pivot table in query ?
                  913349

                  Thanks a lot..

                  • 6. Re: how to make pivot table in query ?
                    AnnPricks E

                    If your question is answered please acknowledge the question as answered