Crosstab view in Oracle possible?

[drbilbrey]

I am trying to write a "crosstab" view on some joined tables, within an Oracle database. (I have already joined the tables in a view; now I would like to write a crosstab style view based on this view.) In MSAccess, this is ridiculously simple to do with a Crosstab query. In MSSQL (T-SQL), this is less easily done with several CASE statements. How could such a view be created in Oracle 8 (using ANSI-SQL)?

Just so I'm sure I'm making my question clear, here's the situation:

View 1 structure:

item_id item_attribute item_value
000001G i_a_1 d100
000001G i_a_2 e200
000001G i_a_3 f300
000001G i_a_4 g400
000001G ...
000002M i_a_1 d150
000002M i_a_2 e200
000002M i_a_3 f302
000002M i_a_4 g403
000002M ...
000003R i_a_1 d120
...

desired structure of needed crosstab view:

item_id i_a_1 i_a_2 i_a_3 i_a_4 ...
000001G d100 e200 f300 g400 ...
000002M d150 e200 f302 g403 ...
000003R d120 ...
...

Thank you! I apologize if this is a redundant or recurring topic. I searched the tek-tips site for a while before putting up the question. Any help is appreciated!


P.S. To head off possible obvious questions, I am aware of the denormalized table structure that the first view appears to be using. I am constrained to this table structure, unfortunately.

 

[drbilbrey]

Hey, Daniel - since you're experienced in using the CASE statement to imitate a crosstab query on SQL Server, you can similarly use DECODE statements to imitate a crosstab query on Oracle.

I could just leave the answer at that, but heck, I might as well explain things a little better, and continue your example.

SELECT item_id, max(decode(item_attribute, 'i_a_1', NVL(value_meaning, item_value),' ')),
max(decode(item_attribute, 'i_a_2', NVL(value_meaning, item_value),' ')),
max(decode(item_attribute, 'i_a_3', NVL(value_meaning, item_value),' ')),
max(decode(item_attribute, 'i_a_4', NVL(value_meaning, item_value),' '))
FROM View_1
GROUP BY item_id

The NVL function (equivalent to the COALESCE function in SQL Server), as used above, returns the value from another column (item_value) in case your first column (value_meaning) is null.

Oracle doesn't have an MSAccess equivalent for First or Last aggregate functions, but there is an unorthodox way you can use the Max function (which works on either numeric or character datatypes) to return a value, while grouping by the item_id. Now, I used the ' ' (which has a low ASCII value) to force either item_value or value_meaning to be selected first when the max aggregate function operates, but the downside is that you will be returning a space (instead of a null) in the cases where there is no data (record) for an item_attribute.

Hope this helps! Welcome to the wonderful world of Oracle.

 

[drbilbrey]

Wow, that's great, thanks for responding.

But now I have another question. Using the query you so generously took the time to provide, I find that I get the error message "ORA-01467 sort key too long" if I use that max(decode) function more than seven times. I have seventeen item_attributes that I would like to cross tabulate. It seems like Oracle wimps out if I ask it to do too much at once.

But hey, sometimes I can manage to figure things out myself (which can be dangerous sometimes with my piecemeal knowledge and experience base), so I just wrote three views, (the first and second views have seven crosstabbed fields, the third has three, totalling seventeen), and then wrote yet another view to tie the three aforementioned views together.

Of course, I don't know anything about view optimization yet, so I'm sure there's a better approach out there. Maybe someday I'll figure it out. But at least someone was willing to help for now.

Oracle's SQL is wonderful indeed. So powerful.

 

[drbilbrey]

I suppose I neglected to mention why a space had to be returned in the case where a NULL value exists in a cross tabulated column. The reason is that when I use a NULL instead of ' ', and I do an ODBC-connect to the Oracle view through MSAccess, Access cannot, for some reason, read most of the records. It reads a few of them, but then pukes on the rest. If I use a ' ', then MSAccess reads all of the records just fine.

I don't know the solution to this problem, other than I would tend to just blame Oracle, and the SQL dialect it utilizes.

 

[listener22]

My understanding is that the problem lies in ODBC, which does not
handle empty strings well. Jim

oracle, vb