This content has been marked as final. Show 7 replies
PLZZ HELP NO HARDCODE PLZZ ONLY DYNAMIC QUERY REQUIRED AND NO PL/SQL USED..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.
OK NOT USING DYNAMIC QUERY ..
JUST SUPPOSE I HAVE 4 ALLOWANCE
NOW PLZZ CONVERT INTO COLUMNS..
search this forum for "row to column"
and to add to Karthick: get a different keyboard, yours seems to be stuck in Uppercase...
yours seems to be stuck in Uppercase...Not true:
RANCHooMust be something else. Maybe a random error?
You might get more useful answers if you have a meaningful subject for your post,
Fix the missing letters from your keyboard and stop shouting.
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:
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.
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
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.
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
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.