1 2 Previous Next 15 Replies Latest reply on May 12, 2017 2:53 PM by 3409791

    convert columns into row and row into columns in a single query

    3409791

      Hi All,

      Thanks for your time. I have a table with metrics/facts as separate column (Horizontal) and a fiscal period column (Vertical) -

      I need to convert the each fact/metric column into row and the fiscal period values to be each column -

       

      The values in FISCAL_PD are updated every month so in June first week we get 201706  -

      screenshot1.png

       

      LIKE

      Fact_NAME              201701     201702     201703     201704     201705

      FACT1                         100          100          100          100               100

      FACT2                         200           200         200          200               200

      FACT3                         300          300          300          300               300

      FACT4                         400          400          400          400               400

      FACT5                          500         500          500          500               500

      FACT6                         600          600          600          600               600

      FACT7                          700          700          700          700               700

      FACT8                         800          800          800          800               800

      FACT9                         900          900          900          900               900

      FACT10                         1000     1000          1000       1000              1000

       

      Could anyone please help with the best option -

       

      CREATE TABLE TEST_DATA

      (

      FISCAL_PD   VARCHAR2(8),

      FACT1       NUMBER(38,2),

      FACT2       NUMBER(38,2),

      FACT3       NUMBER(38,2),

      FACT4       NUMBER(38,2),

      FACT5       NUMBER(38,2),

      FACT6       NUMBER(38,2),

      FACT7       NUMBER(38,2),

      FACT8       NUMBER(38,2),

      FACT9       NUMBER(38,2),

      FACT10      NUMBER(38,2)

      );

      INSERT INTO TEST_DATA VALUES('201701',100,200,300,400,500,600,700,800,900,1000)

      INSERT INTO TEST_DATA VALUES('201702',100,200,300,400,500,600,700,800,900,1000)

      INSERT INTO TEST_DATA VALUES('201703',100,200,300,400,500,600,700,800,900,1000)

      INSERT INTO TEST_DATA VALUES('201704',100,200,300,400,500,600,700,800,900,1000)

      INSERT INTO TEST_DATA VALUES('201705',100,200,300,400,500,600,700,800,900,1000)

       

       

      Please let me know if you need any further information - Thanks a lot for your time

        • 1. Re: convert columns into row and row into columns in a single query
          Frank Kulash

          Hi,

           

          Taking data from different rows, and displaying it as different columns is called Pivoting.

          Taking data from different columns and displaying it as different rows is called Unpivoting.

          You can do both in a single query: first, unpivot the data so all the facts are in 1 column, then pivot that data so that fact1, fact2, ..., fact10 are separate columns.

           

          EDIT:  The rest of this reply is wrong.  See reply #3 for the correct version.

           

          Here's one way to do that:

          SELECT    *

          FROM      test_data

          UNPIVOT   (    fact

                    FOR  col  IN ( fact1, fact2, fact3, fact4, fact5

                                 , fact6, fact7, fact8, fact9, fact10

                                 )

                    )

          PIVOT     (    MIN (fact)

                    FOR  col  IN ( 'FACT1'   AS fact1

                                 , 'FACT2'   AS fact2

                                 , 'FACT3'   AS fact3

                                 , 'FACT4'   AS fact4

                                 , 'FACT5'   AS fact5

                                 , 'FACT6'   AS fact6

                                 , 'FACT7'   AS fact7

                                 , 'FACT8'   AS fact8

                                 , 'FACT9'   AS fact9

                                 , 'FACT10'  AS fact10

                                 )

                    )

          ORDER BY  fiscal_pd

          ;

          Output from your sample data:

          FISCAL_P  FACT1  FACT2  FACT3  FACT4  FACT5  FACT6  FACT7  FACT8  FACT9 FACT10

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

          201701      100    200    300    400    500    600    700    800    900   1000

          201702      100    200    300    400    500    600    700    800    900   1000

          201703      100    200    300    400    500    600    700    800    900   1000

          201704      100    200    300    400    500    600    700    800    900   1000

          201705      100    200    300    400    500    600    700    800    900   1000

          Does fiscal_period always correspond to a month?  If so, it seems like fiscal_period should be a DATE, not a VARCHAR2.

          Given that it's a VARCHAR2, why is the length 8, rather than 6?

          • 2. Re: convert columns into row and row into columns in a single query
            Mike Kutz

            You do realize that you just PIVOT-ed the table back to the way its original form? 

             

            For the OP:

            PIVOT requires that the final list of columns is static.

            If the column names need to be dynamic (because it is now 2018), then you need to complete the PIVOT step at the Display Tier.

             

            MK

            • 3. Re: convert columns into row and row into columns in a single query
              Frank Kulash

              Hi,

              Mike Kutz wrote:

               

              You do realize that you just PIVOT-ed the table back to the way its original form?

              ...

              So I did.  Sorry about that; I checked to make sure my results matched what OP posted, but the input and output weren't clearly marked.

               

              Here's what I should have posted:

              SELECT    *

              FROM      test_data

              UNPIVOT   (    fact

                        FOR  col  IN ( fact1, fact2, fact3, fact4, fact5

                                     , fact6, fact7, fact8, fact9, fact10

                                     )

                        )

              PIVOT     (    MIN (fact)

                        FOR  fiscal_pd  IN ( '201701'  AS p_201701

                                           , '201702'  AS p_201702

                                           , '201703'  AS p_201703

                                           , '201704'  AS p_201704

                                           , '201705'  AS p_201705

                                           )

                        )

              ORDER BY  TO_NUMBER (SUBSTR (col, 5))

              ;

              Output:

              COL    P_201701 P_201702 P_201703 P_201704 P_201705

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

              FACT1       100      100      100      100      100

              FACT2       200      200      200      200      200

              FACT3       300      300      300      300      300

              FACT4       400      400      400      400      400

              FACT5       500      500      500      500      500

              FACT6       600      600      600      600      600

              FACT7       700      700      700      700      700

              FACT8       800      800      800      800      800

              FACT9       900      900      900      900      900

              FACT10     1000     1000     1000     1000     1000

              • 4. Re: convert columns into row and row into columns in a single query
                3409791

                Hi Frank,

                Thank you so much for your time.. I am confused to get the both in one query..

                 

                Actually, I have a table with the Fiscal_PD with values like 201701,201702,201703,201704,201705 then we get 201706 next month and so on - need each month as a column

                Along with my facts there are some other columns also in my table which I do not need - for my query purpose I only need these 10 facts and fiscal pd only

                I also need to use the SUM on each fact - the data is detail I have to sum it

                 

                 

                Facts should be as Rows and Fiscal_PD values should be columns

                 

                Could you please help - I really appreciate your time

                 

                thanks a lot

                • 5. Re: convert columns into row and row into columns in a single query
                  3409791

                  Hi Frank,

                  sorry I think I should have explained clearly -- so the table I have other columns also which I do not need, I only need the fiscal_pd and the 10 facts

                   

                  FISCAL_PD      EMP_NBR           FACT1          FACT2     FACT3

                  201701               101                    100               100               120

                  201702               101                    120               100               130    

                  201703               101                    130               100               130

                   

                  I need to use SUM also with pivot and unpivot

                   

                  when I use the fiscal_pd something like this it errors out - could you please help me out with this

                   

                  SELECT FISCAL_PD

                  FROM TEST_DATA

                  PIVOT (SUM(FACT1) FOR FISCAL_PD IN ('201701','201702','201703','201704','201705','201706','201707','201708','201709','201710','2017011','201712'))

                   

                  thanks a lot

                  • 6. Re: convert columns into row and row into columns in a single query
                    Frank Kulash

                    Hi,

                    3409791 wrote:

                     

                    Hi Frank,

                    Thank you so much for your time.. I am confused to get the both in one query..

                     

                    Actually, I have a table with the Fiscal_PD with values like 201701,201702,201703,201704,201705 then we get 201706 next month and so on - need each month as a column

                    Along with my facts there are some other columns also in my table which I do not need - for my query purpose I only need these 10 facts and fiscal pd only

                    I also need to use the SUM on each fact - the data is detail I have to sum it

                     

                     

                    Facts should be as Rows and Fiscal_PD values should be columns

                     

                    Could you please help - I really appreciate your time

                     

                    thanks a lot

                    SELECT ... PIVOT    assumes all columns in the table are significant, so in your case (and many others) all the unnecessary columns need to be removed in a sub-query.  If you need to do any joins or filtering, do them in the sub-query, also.

                     

                    For example:

                    WITH    relevant_data  AS

                    (

                        SELECT  fiscal_pd

                        ,       fact1, fact2, fact3, fact4, fact5

                        ,       fact6, fact7, fact8, fact9, fact10

                        FROM    test_data

                    --  JOIN    ...

                    --  WHERE   ...

                    )

                    SELECT    *

                    FROM      relevant_data   -- NOT test_data

                    UNPIVOT   (    fact

                              FOR  col  IN ( fact1, fact2, fact3, fact4, fact5

                                           , fact6, fact7, fact8, fact9, fact10

                                           )

                              )

                    PIVOT     (    MIN (fact)

                              FOR  fiscal_pd  IN ( '201701'  AS p_201701

                                                 , '201702'  AS p_201702

                                                 , '201703'  AS p_201703

                                                 , '201704'  AS p_201704

                                                 , '201705'  AS p_201705

                                                 )

                              )

                    ORDER BY  TO_NUMBER (SUBSTR (col, 5))

                    ;

                    As Mike said in reply #2, PIVOT requires a static list of values yo identify the columns, and static column names.  If you always want to show the current month and 4 previous months, then you can use the MONTHS_BETWEEN function to number the desired months 0 through 4, but you'll have to use generic static column names, like MONTH_0, ..., MONTH4.

                     

                    As Mike said, you can use dynamic SQL to get output where the names of the columns, or the number of columns, are not static.

                     

                    One work-around is XML output, where Oracle does indeed produce a static column, but inside that static column is a dynamic number of '<td>' elements.

                    Another (similar) work-around is to use string concatenation to produce one huge VARCHAR2 column that is formatted to look like a variable (dynamic) number of columns.

                     

                    See Re: Report count and sum from many rows into many columns

                    for other options.

                    • 7. Re: convert columns into row and row into columns in a single query
                      Frank Kulash

                      Hi,

                      3409791 wrote:

                       

                      Hi Frank,

                      sorry I think I should have explained clearly -- so the table I have other columns also which I do not need, I only need the fiscal_pd and the 10 facts

                       

                      FISCAL_PD EMP_NBR FACT1 FACT2 FACT3

                      201701 101 100 100 120

                      201702 101 120 100 130

                      201703 101 130 100 130

                       

                      I need to use SUM also with pivot and unpivot

                       

                      when I use the fiscal_pd something like this it errors out - could you please help me out with this

                       

                      SELECT FISCAL_PD

                      FROM TEST_DATA

                      PIVOT (SUM(FACT1) FOR FISCAL_PD IN ('201701','201702','201703','201704','201705','201706','201707','201708','201709','201710','2017011','201712'))

                       

                      thanks a lot

                      The SELECT clause operates on the results of the PIVOT clause.  Change the SELECT clause to

                      SELECT  *

                      to see what columns are available for you to use in the SELECT clause.  You'll notice that there is no column called FISCAL_PD; the PIVOT clause replaced it with columns called "'201701'", ... "'201712'".

                       

                      As always, if you need help, post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from  that sample data.  Make sure the sample data reflects your real data, and what you want to do with it.  Does your real data include multiple rows for the same fiscal_pd?  Then post some sample data with multiple rows for the same fiscal_pd.  Does your real data include columns that play no role in this problem, but need to be ignored so they don't affect the PIVOT?  Then post sample data that has columns that play no role in this problem.

                       

                      Simplify the problem.  You may need to unpivot 10 fact columns in your real problem, but you can post an example where you only need to unpivot 3 fact columns.  (If you want, you can leave the extra fact columns in the sample data, as examples of the irrelevant columns that you need to ignore.)

                      • 8. Re: convert columns into row and row into columns in a single query
                        Mike Kutz

                        My favorite response is:

                        Make the report engine do its job (which is to PIVOT and SUM the data)

                         

                         

                        APEX IR can do it

                        BI Publisher can do it.

                        I've seen ETLs do it.

                         

                        Why aren't people using the tool for what it was designed to do?  (rhetorical question)

                         

                        I have no idea what the OP is using for a "Report Engine".  But, I'm sure it can be done.

                         

                        My $0.02

                         

                        MK

                        • 9. Re: convert columns into row and row into columns in a single query
                          3409791

                          Hi Frank,

                          Thanks a lot. I should have clearly explained with the sample data as it is in my table. I/we would have saved lot of time ...sorry about that

                           

                          So I have a table something like this - This is a detail level table with employee data for each month; Each fiscal period each employee can have multiple records because of the different tasks an employee is working on

                          I want to aggregate the data by Fiscal Period and  get the 10 facts  - my table will only have 12 months of data for the year; every year we archive the table in December - Every month after the process we get new value like

                          for ex: 201706 for June of 2017

                           

                          what I want is

                          FACT_NAME     201601     201602          201603          201604     201605     201606     201607      201608         ----               201612

                          FACT1                   10          20                    30                    40          50          60                    70           80                                   100

                          FACT2                    10         20                   30                   40          50          60                    70          80               --                    100

                          ---

                          ----

                          FACT10               10           20                  30               40               50          60               70               80              --                         100

                           

                            we use a tool called Oracle Hyperion Interactive Reporting - In which I have two compare two data sources 1 source is from Hyperion Essbasse in which I get the data in the above format

                          so I need to put the query from source 2 in the above format so that I can do a SOURCE 1 MINUS SOURCE 2 - For this purpose I need to arrange my data in source 2 in the above format

                           

                          thanks a lot

                           

                           

                          CREATE TABLE  TEST_101

                          (

                          FISCAL_PD          ` VARCHAR2(6),

                          EMP_NBR             VARCHAR2(6),

                          EMP_NAME            VARCHAR2(100),

                          LOCATION_NAME    VARCHAR2(50),

                          TASK_NAME           VARCHAR2(50),

                          FACT1               NUMBER(38,2),

                          FACT2               NUMBER(38,2),

                          FACT3               NUMBER(38,2),

                          FACT4               NUMBER(38,2),

                          FACT5               NUMBER(38,2),

                          FACT6               NUMBER(38,2),

                          FACT7               NUMBER(38,2),

                          FACT8               NUMBER(38,2),

                          FACT9               NUMBER(38,2),

                          FACT10             NUMBER(38,2)

                          );

                          SAMPLE DATA FOR 3 EMPLOYEES FOR 3 PERIODS/MONTHS

                          INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                          INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)

                          • 10. Re: convert columns into row and row into columns in a single query
                            Frank Kulash

                            Hi,

                            3409791 wrote:

                             

                            Hi Frank,

                            Thanks a lot. I should have clearly explained with the sample data as it is in my table. I/we would have saved lot of time ...sorry about that

                             

                            So I have a table something like this - This is a detail level table with employee data for each month; Each fiscal period each employee can have multiple records because of the different tasks an employee is working on

                            I want to aggregate the data by Fiscal Period and get the 10 facts - my table will only have 12 months of data for the year; every year we archive the table in December - Every month after the process we get new value like

                            for ex: 201706 for June of 2017

                             

                            what I want is

                            FACT_NAME 201601 201602 201603 201604 201605 201606 201607 201608 ---- 201612

                            FACT1 10 20 30 40 50 60 70 80 100

                            FACT2 10 20 30 40 50 60 70 80 -- 100

                            ---

                            ----

                            FACT10 10 20 30 40 50 60 70 80 -- 100

                             

                            we use a tool called Oracle Hyperion Interactive Reporting - In which I have two compare two data sources 1 source is from Hyperion Essbasse in which I get the data in the above format

                            so I need to put the query from source 2 in the above format so that I can do a SOURCE 1 MINUS SOURCE 2 - For this purpose I need to arrange my data in source 2 in the above format

                             

                            thanks a lot

                             

                             

                            CREATE TABLE TEST_101

                            (

                            FISCAL_PD ` VARCHAR2(6),

                            EMP_NBR VARCHAR2(6),

                            EMP_NAME VARCHAR2(100),

                            LOCATION_NAME VARCHAR2(50),

                            TASK_NAME VARCHAR2(50),

                            FACT1 NUMBER(38,2),

                            FACT2 NUMBER(38,2),

                            FACT3 NUMBER(38,2),

                            FACT4 NUMBER(38,2),

                            FACT5 NUMBER(38,2),

                            FACT6 NUMBER(38,2),

                            FACT7 NUMBER(38,2),

                            FACT8 NUMBER(38,2),

                            FACT9 NUMBER(38,2),

                            FACT10 NUMBER(38,2)

                            );

                            SAMPLE DATA FOR 3 EMPLOYEES FOR 3 PERIODS/MONTHS

                            INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                            INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)

                            Sorry, I don't see how you get those results from the given data.  For example, for the FACT1 row, why is the "201601" value 10, and not 90?

                            Are you sure what you posted is really what you want from the given data?

                             

                            At any rate, use a sub-query to get only the columns you need in this problem.

                            You can call SUM in the sub-query, or in the main PIVOT clause, or in both.  In this case, it seems like calling SUM in the main PIVOT clause, and noot in the sub-query, would be the simplest way. (That is, the query in reply #6 is close to what I think you want; only use SUM instead of MIN in the PIVOT clause.)

                            • 11. Re: convert columns into row and row into columns in a single query
                              3409791

                              Hi Frank, that should have been the sum of all - sorry for the confusion

                              I just gave you the sample data for the last year which is 2016 - so in the entire 2016 we will have values in FISCAL_PD like 201601 to 201612 and each fact like FACT1, FACT2,  Etc. will have a value for each month

                              I only gave you the sample data for 3 employees for 3 periods - Each employee has 3 different tasks each month - 3 * 9 = 27 records

                               

                              Let me explain clearly - starting with the table definition  - for the employee 000001 for 201601 we have 3 different tasks

                               

                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)

                               

                              for the employee 000001 for 201602 we have 3 different tasks

                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)

                               

                              for the employee 000001 for 201603 we have 3 different tasks

                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)

                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)

                               

                              CREATE TABLE TEST_101

                              (

                              FISCAL_PD `                VARCHAR2(6),

                              EMP_NBR                     VARCHAR2(6),

                              EMP_NAME                  VARCHAR2(100),

                              LOCATION_NAME      VARCHAR2(50),

                              TASK_NAME                VARCHAR2(50),

                              FACT1                          NUMBER(38,2),

                              FACT2                          NUMBER(38,2),

                              FACT3                          NUMBER(38,2),

                              FACT4                          NUMBER(38,2),

                              FACT5                          NUMBER(38,2),

                              FACT6                          NUMBER(38,2),

                              FACT7                          NUMBER(38,2),

                              FACT8                          NUMBER(38,2),

                              FACT9                          NUMBER(38,2),

                              FACT10                        NUMBER(38,2)

                              );

                               

                              ALL INSERT STATEMENTS

                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000001','AAA','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000002','BBB','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201601','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201602','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X1',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X2',10,20,30,40,50,60,70,80,90,100)
                              INSERT INTO TEST_101 VALUES ('201603','000003','CCC','USA','X3',10,20,30,40,50,60,70,80,90,100)

                               

                              now I need to aggregate at the FISCAL_PD LEVEL only and at the same time transpose FACT1 to FACT10 as ROWS and Transpose values in FISCAL_PD into separate column values

                               

                                                  2016011     201602     201603

                              FACT1          90               90               90           

                              FACT2          180              180          180

                              FACT3          270               270          270

                              Etc.

                               

                              Thanks a lot for your time, sorry for the confusion

                              • 12. Re: convert columns into row and row into columns in a single query
                                Frank Kulash

                                Hi,

                                 

                                It looks like the query in reply #6 is what you want, only using SUM instead on MIN in the PIVOT clause.

                                 

                                That is:

                                WITH    relevant_data    AS

                                (

                                    SELECT  SUBSTR (fiscal_pd, 5)  AS  month

                                    ,       fact1, fact2, fact3

                                --  ...                            -- put other fact columns here

                                    FROM    test_101

                                    WHERE   SUBSTR (fiscal_pd, 1, 4)  = '2016'  -- if necessary

                                )

                                SELECT    *

                                FROM      relevant_data

                                UNPIVOT   (     fact_value

                                          FOR  fact_name  IN ( fact1, fact2, fact3

                                                         --   ...   -- put other fact columns here

                                                             )

                                          )

                                PIVOT     (     SUM (fact_value)

                                          FOR  month  IN  ( '01'  AS jan

                                                          , '02'  AS feb

                                                          , '03'  AS mar

                                                      --   ...      -- put other months here

                                                          )

                                          )

                                ORDER BY  fact_name

                                ;

                                Output:

                                FACT_NAME     JAN     FEB     MAR

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

                                FACT1          90      90      90

                                FACT2         180     180     180

                                FACT3         270     270     270

                                Avoid non-standard column names, such as "201601" (starting with a digit).  If you're just using this as the 2nd part of a MINUS operation, it won't matter what the column names are, so you might as well use something meaningful and easy to type, like JAN.  But if you really, really want non-standard names like "201601", use them where I used JAN, FEB, and so on.

                                • 13. Re: convert columns into row and row into columns in a single query
                                  3409791

                                  Hi Frank, Thanks for your time, I have few questions could you please help me understand

                                   

                                  1). since my table holds is detail level data - without SUM if I just run SELECT FISCAL_PD, FACT1,.....FACT1 from my_table - this query gives me 2,174,289 - (2 millions plus)

                                  2) Actually if I have 12 months of data I would only get 12 records or rows for each month i.e. JAn to DEC

                                   

                                  3). Can I not use the SUM in the below in RED with a GROUP BY so that I don't have to read 2 million records - I really appreciate your time

                                   

                                  WITH    relevant_data    AS

                                  (

                                      SELECT  SUBSTR (fiscal_pd, 5)  AS  month

                                      ,      fact1, fact2, fact3

                                  --  ...                            -- put other fact columns here

                                      FROM    test_101

                                      WHERE  SUBSTR (fiscal_pd, 1, 4)  = '2016'  -- if necessary

                                  )

                                  SELECT    *

                                  FROM      relevant_data

                                  UNPIVOT  (    fact_value

                                            FOR  fact_name  IN ( fact1, fact2, fact3

                                                          --  ...  -- put other fact columns here

                                                              )

                                            )

                                  PIVOT    (    SUM (fact_value)

                                            FOR  month  IN  ( '01'  AS jan

                                                            , '02'  AS feb

                                                            , '03'  AS mar

                                                        --  ...      -- put other months here

                                                            )

                                            )

                                  ORDER BY  fact_name

                                  • 14. Re: convert columns into row and row into columns in a single query
                                    Frank Kulash

                                    Hi,

                                    3409791 wrote:

                                     

                                    Hi Frank, Thanks for your time, I have few questions could you please help me understand

                                    I'll try, but I don't understand some of the questions.

                                    1). since my table holds is detail level data - without SUM if I just run SELECT FISCAL_PD, FACT1,.....FACT1 from my_table - this query gives me 2,174,289 - (2 millions plus)

                                    Like this one.  What is the question?

                                    2) Actually if I have 12 months of data I would only get 12 records or rows for each month i.e. JAn to DEC

                                    If you only have 12 rows for each month, and there are 12 months in a year, then you should only have 12 * 12 = 144 rows per year.  So if you have 2174289 rows in the table, that means your data covers 2174289 / 144 = 15099 years.  That sounds odd.

                                    Are you saying that there are only 144 rows for the year 2016, but other years may have more?

                                    How is fiscal_pd indexed?

                                    For all performance issues, post the execution plan.  See the Forum FAQ: Re: 3. How to improve the performance of my query? / My query is running slow.

                                     

                                    Again, what is the question?  Both question 1) and question 2) seem more like statements than questions.

                                     

                                    3). Can I not use the SUM in the below in RED with a GROUP BY so that I don't have to read 2 million records - I really appreciate your time

                                     

                                    WITH relevant_data AS

                                    (

                                    SELECT SUBSTR (fiscal_pd, 5) AS month

                                    , fact1, fact2, fact3

                                    -- ... -- put other fact columns here

                                    FROM test_101

                                    WHERE SUBSTR (fiscal_pd, 1, 4) = '2016' -- if necessary

                                    )

                                    SELECT *

                                    FROM relevant_data

                                    UNPIVOT ( fact_value

                                    FOR fact_name IN ( fact1, fact2, fact3

                                    -- ... -- put other fact columns here

                                    )

                                    )

                                    PIVOT ( SUM (fact_value)

                                    FOR month IN ( '01' AS jan

                                    , '02' AS feb

                                    , '03' AS mar

                                    -- ... -- put other months here

                                    )

                                    )

                                    ORDER BY fact_name

                                     

                                    No, there's nothing you can do in the SELECT clause that restricts how many rows are read.  The WHERE clause is the part that limits how many rows are read.  If the WHERE clause should be limiting the query to 144 rows, but you're reading 2 million, then that's the performance problem.  Nothing you do with the 144 rows will be nearly as costly as reading all those extra rows.

                                     

                                    If all the rows are being read despite the WHERE clause, then you can try changing it as shown in blue, below.

                                     

                                    As mentioned in reply #10, you can call SUM in the sub-query, or the main query, or both.  You can try calling SUM in both, like this:

                                    WITH    relevant_data    AS

                                    (

                                        SELECT    SUBSTR (fiscal_pd, 5)  AS  month

                                        ,         SUM (fact1)            AS fact1

                                        ,         SUM (fact2)            AS fact2

                                        ,         SUM (fact3)            AS fact3

                                    --  ...                            -- put other fact columns here

                                        FROM      test_101

                                        WHERE     fiscal_pd  BETWEEN  '201601'

                                                             AND      '201612'

                                        GROUP BY  SUBSTR (fiscal_pd, 5)

                                    )

                                    SELECT    *

                                    FROM      relevant_data

                                    UNPIVOT   (    fact_value

                                              FOR  fact_name  IN ( fact1, fact2, fact3

                                                            --  ...  -- put other fact columns here

                                                                )

                                              )

                                    PIVOT     (    SUM (fact_value)

                                              FOR  month  IN  ( '01'  AS jan

                                                              , '02'  AS feb

                                                              , '03'  AS mar

                                                          --  ...      -- put other months here

                                                              )

                                              )

                                    ORDER BY  fact_name

                                    ;

                                    but, if the sub-query is only producing 144 rows, the difference will be insignificant.

                                     

                                    This is just a guess at what might improve the performance. Guessing is not the best way to solve problems.  It's much better to look at the execution plan to see what's actually causing the performance issue.

                                    1 2 Previous Next