7 Replies Latest reply on Jan 26, 2010 2:14 PM by Frank Kulash

    Please Help in query

    747821
      SELECT PEE2.ASSIGNMENT_ID,--,PEEV2.EFFECTIVE_START_DATE,
      PET.ELEMENT_NAME,NVL(MAX(PEEV2.SCREEN_ENTRY_VALUE),0)SALARY
      --PEE2.EFFECTIVE_START_DATE PEE_START
      FROM PAY_ELEMENT_ENTRIES_F PEE2,PAY_ELEMENT_ENTRY_VALUES_F PEEV2 , PAY_ELEMENT_TYPES_F PET
      WHERE PEE2.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
      AND PEE2.ELEMENT_ENTRY_ID=PEEV2.ELEMENT_ENTRY_ID
      AND PET.ELEMENT_NAME IN('BASIC_SALARY','AL_CONVEYANCE','AL_TEA','AL_COLA')
      AND PEEV2.SCREEN_ENTRY_VALUE IS NOT NULL
      AND (PEE2.ASSIGNMENT_ID,PEEV2.EFFECTIVE_START_DATE)=(
      SELECT PEE.ASSIGNMENT_ID,MAX(PEEV.EFFECTIVE_START_DATE)
      FROM PAY_ELEMENT_ENTRIES_F PEE,PAY_ELEMENT_ENTRY_VALUES_F PEEV
      WHERE PEE.ELEMENT_ENTRY_ID=PEEV.ELEMENT_ENTRY_ID
      AND PEE.ASSIGNMENT_ID=:ASSIGNMENT_ID
      AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
      AND PET.ELEMENT_NAME IN('BASIC_SALARY','AL_CONVEYANCE','AL_TEA','AL_COLA')
      AND PEEV.SCREEN_ENTRY_VALUE IS NOT NULL
      GROUP BY PEE.ASSIGNMENT_ID
      )GROUP BY ASSIGNMENT_ID , PET.ELEMENT_TYPE_ID , PET.ELEMENT_NAME


      output is
      ASSIGNMENT_ID     ELEMENT_NAME     SALARY

      117     AL_COLA     650
      117     AL_CONVEYANCE     1180
      117     AL_TEA     172
      117     BASIC_SALARY     7820


      but i want the output like this
      ASSIGNMENT_ID ------AL_COLA------AL_CONVEYANCE------AL_TEA-------BASIC_SALARY
      117---------------------------650-------------------1180----------------------172-----------7820

      PLZZ HELP NO HARDCODE PLZZ ONLY DYNAMIC QUERY REQUIRED AND NO PL/SQL USED..
      REGARDS
      RANCHOO
        • 1. Re: Please Help in query
          Karthick2003
          PLZZ HELP NO HARDCODE PLZZ ONLY DYNAMIC QUERY REQUIRED AND NO PL/SQL USED..
          REGARDS
          RANCHOO
          NOT POSSIBLE. Why? Number of column in a SQL SELECT statement is static. It must be known to the SQL engine when it parses it.

          What you are asking is a Presentation layer work. Do it using some reporting tool that you use. You can do it in SQL only when you know exactly how many columns you have in your select statement.
          • 2. Re: Please Help in query
            747821
            OK NOT USING DYNAMIC QUERY ..
            JUST SUPPOSE I HAVE 4 ALLOWANCE
            NOW PLZZ CONVERT INTO COLUMNS..

            REGARDS
            RANCHoo
            • 3. Re: Please Help in query
              Karthick2003
              search this forum for "row to column"
              • 4. Re: Please Help in query
                21205
                and to add to Karthick: get a different keyboard, yours seems to be stuck in Uppercase...


                ;)
                • 5. Re: Please Help in query
                  Peter Gjelstrup
                  yours seems to be stuck in Uppercase...
                  Not true:
                  RANCHoo
                  Must be something else. Maybe a random error?
                  • 6. Re: Please Help in query
                    6363
                    You might get more useful answers if you have a meaningful subject for your post,

                    http://www.catb.org/~esr/faqs/smart-questions.html#bespecific

                    Fix the missing letters from your keyboard and stop shouting.

                    http://www.catb.org/~esr/faqs/smart-questions.html#writewell
                    • 7. Re: Please Help in query
                      Frank Kulash
                      Hi,

                      Since you didn't post any tables, I'll use the scott.emp table to show you how to get results like that.

                      Let's say we're interested in this data:
                      SELECT       deptno
                      ,       job
                      ,       sal
                      FROM       scott.emp
                      ORDER BY  deptno
                      ,     job
                      ;
                      
                          DEPTNO JOB              SAL
                      ---------- --------- ----------
                              10 CLERK           1300
                              10 MANAGER         2450
                              10 PRESIDENT       5000
                              20 ANALYST         3000
                              20 ANALYST         3000
                              20 CLERK           1100
                              20 CLERK            800
                              20 MANAGER         2975
                              30 CLERK            950
                              30 MANAGER         2850
                              30 SALESMAN        1250
                              30 SALESMAN        1250
                              30 SALESMAN        1600
                              30 SALESMAN        1500
                      Let's say we want to get a total sal for each deptno and job, aned present it as a cross-tab, with a row for every deptno and a column for every job. As Karthick pointed out, that requires dynamic SQL, so let's just take the first 4 jobs in alphabetic order. The query below numbers the jobs 1, 2, 3, ... in alphabetic order, then uses that number to consider only a certain job in each column.
                      WITH     got_jnum    AS
                      (
                           SELECT       deptno
                           ,       job
                           ,       sal
                           ,       DENSE_RANK () OVER (ORDER BY job)     AS jnum
                           FROM       scott.emp
                      )
                      SELECT       deptno
                      ,       TO_CHAR (SUM (CASE WHEN jnum = 1 THEN sal END), '99999999')     AS job_1
                      ,       TO_CHAR (SUM (CASE WHEN jnum = 2 THEN sal END), '99999999')     AS job_2
                      ,       TO_CHAR (SUM (CASE WHEN jnum = 3 THEN sal END), '99999999')     AS job_3
                      ,       TO_CHAR (SUM (CASE WHEN jnum = 4 THEN sal END), '99999999')     AS job_4
                      FROM       got_jnum
                      GROUP BY  deptno
                                   --
                          UNION ALL
                             --
                      SELECT       NULL                                   AS deptno
                      ,       MIN (CASE WHEN jnum = 1 THEN LPAD (job, 9) END)     AS job_1
                      ,       MIN (CASE WHEN jnum = 2 THEN LPAD (job, 9) END)     AS job_2
                      ,       MIN (CASE WHEN jnum = 3 THEN LPAD (job, 9) END)     AS job_3
                      ,       MIN (CASE WHEN jnum = 4 THEN LPAD (job, 9) END)     AS job_4
                      FROM       got_jnum
                             --
                      ORDER BY  deptno     NULLS FIRST
                      ;
                      
                          DEPTNO JOB_1     JOB_2     JOB_3     JOB_4
                      ---------- --------- --------- --------- ---------
                                   ANALYST     CLERK   MANAGER PRESIDENT
                              10                1300      2450      5000
                              20      6000      1900      2975
                              30                 950      2850
                      A lot of the complexity in the query above involves getting the row with the actual job names. If you knew what they were, you could hard-colde them into the query; then you woldn't need the second part of the UNION, and therefore you wouldn't need TO_CHAR.

                      This is called a Pivot . Search for "Pivot" (or, as Karthick suggested "Rows to Columns") to see more examples and explanations.

                      If you really want a variable number of columns, then you need dynamic SQL, as Karthick explained. This isn't very hard, as dynamic SQL goes. You just need to have a preliminary query determine what the jobs are, and write an appropriate SUM expression (with column alias) to use in the SELECT clause. See [this thread|http://forums.oracle.com/forums/message.jspa?messageID=3227306] for an example.

                      Another way to get a variable number of jobs is to use String Aggregation , where all the data about all the jobs is combined in one giant string column. That huge string can be formatted so that it looks like separate columns. [This site|http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php] has a good summary of different String Aggregation techniques.