Pivot data from multiple rows onto one field - oracle 10 sql syntax

Please see the attached which displays fig1 sample data and fig2. desired output.

I want to concatenate data from multiple rows in one column into one field and join them with the word 'and'.

Any help with the syntax would be appreciated
sample.xls
tonMachine100Asked:
Who is Participating?
 
POracleConnect With a Mentor Commented:
Hi Try this,

Create Table test as select table_name from user_tables;

SELECT * FROM (
  SELECT LEVEL, SYS_CONNECT_BY_PATH(TABLE_NAME,' AND '') RES,  LENGTH(SYS_CONNECT_BY_PATH(TABLE_NAME,',')) LN
FROM TEST
START WITH TABLE_NAME='First_Table_row'
CONNECT BY NOCYCLE PRIOR TABLE_NAME<>TABLE_NAME
) ORDER BY LN DESC
/

You will get multiple row from this, modify query using where clause to filter row that you want and get the one row which satisfy your requirement.
0
 
wietmanCommented:
So in this case, you are not grouping the data in any way.  You want a particular column of every single row pivoted.  Am I restating this correctly?
0
 
tonMachine100Author Commented:
yes thats correct
0
 
wietmanCommented:
Usually , there is some kind of grouping in a pivot and we would also need to explicitly group by known fields.  You example does not fit here.  It seems to me this really requires a much more simple example.

What about simply:

select per_name||' and '  from table_name

This would, of course leave an extra 'and' at the end but if I'm on the right track, I think I could fix that.
It also puts a CR after each 'and'.  There are ways around that , too.  Just let me know what is or is not acceptable with this solution.  I can solve both of these issues with an anonymous pl/sql block.  just let me know how you would like me to proceed.
0
 
wietmanConnect With a Mentor Commented:
Here's mine:

set serveroutput on
declare
    create_string   varchar2(32000) := null;
    rn number := 0;
begin
dbms_output.enable(1000000);
  for x in (select  rownum rn, table_name from ( select table_name from user_tables ))
    loop
        if x.rn > 1
        then
            create_string := create_string||' and '||x.table_name;
        else
            create_string := x.table_name;
        end if;
    end loop;
 dbms_output.put_line(create_string);
end;
/
0
All Courses

From novice to tech pro — start learning today.