Forum Stats

  • 3,734,306 Users
  • 2,246,942 Discussions
  • 7,857,222 Comments

Discussions

Please Help in query

747821
747821 Member Posts: 245
edited Jan 26, 2010 9:14AM in SQL & PL/SQL
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

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    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.
  • 747821
    747821 Member Posts: 245
    OK NOT USING DYNAMIC QUERY ..
    JUST SUPPOSE I HAVE 4 ALLOWANCE
    NOW PLZZ CONVERT INTO COLUMNS..

    REGARDS
    RANCHoo
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    search this forum for "row to column"
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    and to add to Karthick: get a different keyboard, yours seems to be stuck in Uppercase...


    ;)
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    yours seems to be stuck in Uppercase...
    Not true:
    RANCHoo
    Must be something else. Maybe a random error?
  • 6363
    6363 Member Posts: 6,642
    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond
    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.
This discussion has been closed.