INSERT INTO CFL (season, team, points) VALUES (2004, 'Tiger-Cats', 19) INSERT INTO CFL (season, team, points) VALUES (2004, 'Alouettes', 28) INSERT INTO CFL (season, team, points) VALUES (2004, 'Argonauts', 21) Let's look a simple example in slow motion.ĬREATE TABLE CFL (season NUMBER(4), team VARCHAR2(16), points NUMBER(3)) It clearly shows how you use decode to create a "sparse" matrix, and then use max to "squash" it down. Here is another one of Ask Tom's example. (Note: it gets more complicated if you don't know how many columns you'll need).
Use "max" to "squash" the multiple rows you moved to columns, into single rows. Use "decode" to turn rows into columns (ie. Then use your (revised) original query as a sub-queryģ. Add some kind of count or row number to your query, if necessary for the groupingĢ.
That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.Īs always, when you want to understand something, you can start by Asking Tom:į4950_P8_DISPLAYID,F4950_P8_CRITERIA:766825833740Ī simple pivot query is accomplished by basically doing the following:ġ. In essence, you need to "pivot" rows into columns, or vice versa. Imagine you're trying to create a result set where the rows need to be columns, or vice versa. Here is another advanced concept that will come in useful when solving Oracle problems. You might ask if SQL Developer can do something like COLUMN. The main query works as posted in SQL Developer, but it asks for values for the substitution variables. , MAX (CASE WHEN c_num = 4 THEN unpivoted_data END) AS "&alias_4" , MAX (CASE WHEN c_num = 3 THEN unpivoted_data END) AS "&alias_3" , MAX (CASE WHEN c_num = 2 THEN unpivoted_data END) AS "&alias_2" SELECT MAX (CASE WHEN c_num = 1 THEN unpivoted_data END) AS "&alias_1" , NVL (MIN (CASE WHEN year_num = 4 THEN year_txt END), ' ') AS alias_4_col , NVL (MIN (CASE WHEN year_num = 3 THEN year_txt END), ' ') AS alias_3_col , NVL (MIN (CASE WHEN year_num = 2 THEN year_txt END), ' ') AS alias_2_col SELECT NVL (MIN (CASE WHEN year_num = 1 THEN year_txt END), ' ') AS alias_1_col , DENSE_RANK () OVER (ORDER BY year) AS year_num SELECT TO_CHAR (year, 'fmDD-Mon-YYYY') AS year_txt preliminary query, to define substitution variables Here's how you could do what you want in SQ!L*Plus SQL Developer has qubstitution variables, but I don't know if it has anything comparable to COLUMN. What front-end tool are you using? Can you use SQL*Plus? HEADING command that can do what you want,, but it doesn't simplify this particular problem.) SQL*Plus, for example, has substitution variables, that you can define with data from your table at run-time using the COLUMN. This is probably best done in your front-end. To get variable data, such as '' as a column header, you must use dynamic SQL. \(small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.Ĭolumn names are hard-coded into the query. When posting formatted text (such as results) on this site, type these 6 characters: Revenue3 300 450 500It would have been clearer if you had said: "The result set should be like these 3 rows: Thanks for the reply but this will not make the year as the column header it will be shown as a row in the result set.That's exactly what I thought you wanted, based on your first message: GROUP BY the row identifier, and use the column identifier in a CASE expression.Įdited by: Frank Kulash on 6:57 AMThanks for the reply but this will not make the year as the column header it will be shown as a row in the result set. To pivot, you should have a column that identifies which pivoted row each item belongs to (n above) and which column (c_num). To unpivot, join your data to a table (or, as in this example, a result set) that has one row for every column of your original data. If it is not, change the ORDER BY cluase of the ROW_NUMBER function in sub=query got_unpivoted_data to something that is unique.
, MAX (CASE WHEN c_num = 3 THEN unpivoted_data END) AS column_3 , MAX (CASE WHEN c_num = 2 THEN unpivoted_data END) AS column_2 SELECT MAX (CASE WHEN c_num = 1 THEN unpivoted_data END) AS column_1 WHEN 1 THEN TO_CHAR (t.year, 'fmDD-Mon-YYYY') SELECT DENSE_RANK () OVER (ORDER BY t.year) AS c_num
0 kommentar(er)
