Re: SQL Puzzle

Rob Snieder (mattjones_at_hotpop.com) wrote:
: Is their a way to create a query that combines data from different
: rows into a single row? For example, if you query v$SQLTEXT and pass
: in a hash_value, it will return several rows depending on the length
: of the statement. I would like to be able to query v$SQLTEXT and get
: one row per hash. Is their a way to use connect by, rollup, or some
: other rarely used SQL command to accomplish this? I realize that the
: user could get the sql already combined by querying v$SQLAREA but that
: is not the point of the question. I also realize this could be done
: easily in PL/SQL but I'm looking for a SQL only solution.

One strategy to get multiple rows into one row is to union some queries, putting data into different columns, and then using a group function on each column to get all the data into a single row. No idea if this would help at all here, or be appropriate, and there could be other ways, this one just springs to mind.

example

select max(A),max(B),max(C)
from
(
select table_name A,'' B,'' C from replicate_control where sequence_number = 1
union all
select '',table_name,'' from replicate_control where sequence_number = 2 union all
select '','',table_name from replicate_control where sequence_number = 3 ) Received on Fri Jun 20 2003 - 15:08:58 CDT