SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-22-2008, 05:22 PM
| ||||
| ||||
 Re: No true Crosstab Query in Oracle??? Gavin John Fowler wrote: > Its not conceptually difficult but can be quite tricky if I understand > your problem correctly. > You need to cope with a variable number of 'header' columns in the > output - yes ? (Remember that there may well be an application > receiving this output and this app may expect a fixed number of > coulumns .. so you have to deal with that too ...) > > This is one way. > Each time your corsstabulation report (cause that is what it is) is > run you need to get the 'headers' from some kind of 'SELECT DISTINCT > <header_cols, header_identifiers etc> from <tables> WHERE < > clause> etc etc > Oracle 'dynamic' SQL I am a little new to, but in Sybase & MS SQL > I would build a string representing a correlated sub-query over the > same join as above (in <tables> giving me one column for every > 'header' (or header_identifier) using these 'headers' as parameters in > the WHERE clause. (In Sybase or MS SQL I can use a CURSOR for this but > you could conceivably use another looping construct) The select will, > of course, have an aggregation function such as count etc. > > If your front-end app is expecting 'N' columns then you must ensure > that 'N-number('headers'_found)' 'inert' columns are added by > concatenating any suitably formatted strings representing missing > columns (eg '''N/A'''). > > In my experience cross-tabulation in SQL is messy when you cannot > predict the number of columns returned and there is no way to do this > with 'static' SQL (I believe it is possible to prove this .. see some > of Joe Celko's papers) > > As I am new to Oracle please take this as an idea only, forgive me if > I overlook something obvious. > > Yours, > Gavin > "John W" <John.Wisnieff@oracle.com> wrote in message news:<KD6J9.9$e14.159@news.oracle.com>... > > Ethel, do you have any sample code for this? > > Thanks, > > John > > > > "Ethel Aardvark" <bigjobbies@hotmail.com> wrote in message > > news:1a8fec49.0211270540.4fc248dc@posting.google.c om... > > > Since I don't think it can be done in SQL, it looks like a job for > > > PL/SQL (or some front-end tool, depending on your environment). > > > > > > EA > > > > > > "John W" <John.Wisnieff@oracle.com> wrote in message > > news:<pkNE9.4$Ws2.35@news.oracle.com>... > > > > Ted, > > > > I asume you are referring to the "pivot tables" article. That article is > > > > about creating news rows and not about creating new columns. I am > > looking > > > > for sql code that will create a variable number of columns on the fly. > > > > Thanks, > > > > John > > > > > > > > > > > > "John W" <John.Wisnieff@oracle.com> wrote in message > > > > news:PcuE9.6$4B4.179@news.oracle.com... > > > > > Ted, thanks, I'll review the article. > > > > > > > > > > "Ted McCabe" <theom@gte.net> wrote in message > > > > > news:HjAD9.5818$mL2.5112@nwrddc01.gnilink.net... > > > > > > John: > > > > > > The September/October 2002 Oracle Magazine has a "how to" in it. > > > > > > http://technet.oracle.com/oramag/content.html > > > > > > HTH > > > > > > tED > > > > > > "John W" <John.Wisnieff@oracle.com> wrote in message > > > > > > news:T7AD9.16$lF3.141@news.oracle.com... > > > > > > > I need to do a crosstab query in Oracle where the values from one > > field > > > > > > > become the column headers in the final query result. > > > > > > > An example of this is taking a table that looks like: > > > > > > > > > > > > > > sample_id, test_desc, result > > > > > > > 1, Moisture, 23.4 > > > > > > > 1, Carbon, 32.5 > > > > > > > 1, Sulfur, .0023 > > > > > > > 2, Moisture, 54.2 > > > > > > > 2, Carbon, 12.2 > > > > > > > 2, Sulfur, > > > > > > > 3, Sulfur, .023 > > > > > > > > > > > > > > and returning a result that looks like this: > > > > > > > > > > > > > > sample_id, Moisture, Carbon, Sulfur, ... > > > > > > > 1, 23.4, 23.4, 32.5, .0023 > > > > > > > 2, 54.2, 12.2, > > > > > > > 3, , , .023 > > > > > > > > > > > > > > > > > > > > > However I find no crosstab functionality in Oracle. All I see in > > researching > > > > > > > this is a technique to manually create a DECODE statement for a > > fixed > > number > > > > > > > of columns. Since the number of columns for this query can vary > > this > > option > > > > > > > will not work and this technique is not a true crosstab query. > > Does > > anyone > > > > > > > know of a way to do a crosstab query in Oracle similar to those > > that > > > > can > > > > be > > > > > > > done by desktop databases such as Paradox or MS Acesss? > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Cross-tabulation is easily accomplished using DECODE and CASE. Just alias the resulting columns. To handle an unknown number of columns either use a default clause or use dynamic SQL. Daniel Morgan      |
Linear Mode
