how do i concatinate rows by a requirment

[sonny1974]

sentance ( can be anything typed manually by the user)

i want to concatinate data field,
if the type is the same
line 0001 should be first , then append code (1,2,3,etc)

how would i be able to do this so i can link the sentaces together and there on on seperate rows



TYPE LINE APPEND_CODE DATA

0007 0001 1 sentance
0007 0001 2 sentance
0007 0002 1 sentance
0007 0002 2 sentance

 

[SantaMufasa]

Sonny,

Are you saying that you want multiple sentences for the same LINE to appear concatenated together in a single display column, like this:

TYPE    LINE    DATA
0007    0001    sentence-1 || sentence-2
0007    0002    sentence-1 || sentence-2

What are the ranges of numbers of sentences that will concatenate together? Do you have permission to create and use a user-defined function? If so, then the following code should work nicely for you (if my previous assumptions are correct):

CREATE OR REPLACE FUNCTION stringer (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := trim(hold_result)||' '||trim(incoming);
    end loop;
    return hold_result;
END;
/

Function created.

select * from sonny;

      TYPE       LINE APPEND_CODE DATA
---------- ---------- ----------- --------------------------
         7          1           1 This is sentence number 1.
         7          1           2 This is sentence number 2.
         7          2           1 This is sentence number 1.
         7          2           2 This is sentence number 2.
         7          2           3 This is sentence number 3.
         7          2           4 This is sentence number 4.

6 rows selected.

col sentences format a54
select distinct type,line
      ,stringer('select data from sonny where type = '||type||' and line = '||line) Sentences
  from sonny;

TYPE       LINE SENTENCES
---- ---------- ------------------------------------------------------
   7          1 This is sentence number 1. This is sentence number 2.
   7          2 This is sentence number 1. This is sentence number 2.
                This is sentence number 3. This is sentence number 4.

2 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

is there a way i can do it in my query
dont think i have rights for scripts

 

[SantaMufasa]

Sonny,

Just attempt to execute the "CREATE or REPLACE..." code, above. If it gives you a privilege error, then ask your DBA to do the following:

1) Run the "CREATE OR REPLACE FUNCTION STRINGER..." code
2) CREATE PUBLIC SYNONYM STRINGER FOR STRINGER;
3) GRANT EXECUTE on STRINGER TO PUBLIC;

Since this function is as benign as any other Oracle built-in function, your DBA should see no problem with creating this very powerful function that solves your need.

Once your DBA has created the function, your query would look like this:

SELECT ...STRINGER(<SELECT stmt that returns the rows of the column you want>)
  FROM <some table>;

This is certainly the easiest method to achieve the results you want, I guarantee it!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

so i should send below to my dba and tell him to create a script?

CREATE OR REPLACE FUNCTION stringer (query_in in VARCHAR2) RETURN VARCHAR2 IS
incoming varchar2(4000);
hold_result varchar2(4000);
c sys_refcursor;
Begin
open c for query_in;
loop
fetch c into incoming;
exit when c%notfound;
hold_result := trim(hold_result)||' '||trim(incoming);
end loop;
return hold_result;
END;

 

[SantaMufasa]

so i should send below to my dba and tell him to create a script?

First, your DBA might run the above code as a script to create the STRINGER function, but you don't need DBA permission to create a "script". (Running any script successfully might require greater permissions that you possess, however.)


Your next move depends upon your answers to these questions:

Question 1. Did you already try running the "CREATE OR REPLACE FUNCTION..." code from SQL*Plus, connected with your normal SQL*Plus account?

If the answer is 'Yes', then (Question 2) Did you receive a permission error?

If the answer is 'Yes', then (Q-2 Response-"Yes") you should forward the "CREATE OR REPLACE FUNCTION..." code to your DBA and ask her/him to follow the three steps that I listed, above, for the DBA to do.

(Q-2 Response-"No") You're done...You don't need the DBA's help.

(Q-1 Response-"No") Go back and try running the "CREATE OR REPLACE FUNCTION..." code, then go back through this "Question-Response" logic.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

I Cant do it

is this what i should send to my dba

CREATE OR REPLACE FUNCTION stringer (query_in in VARCHAR2) RETURN VARCHAR2 IS
incoming varchar2(4000);
hold_result varchar2(4000);
c sys_refcursor;
Begin
open c for query_in;
loop
fetch c into incoming;
exit when c%notfound;
hold_result := trim(hold_result)||' '||trim(incoming);
end loop;
return hold_result;
END;

 

[sonny1974]

also there might be times where there are 5 lines that need to in one row.

if the type is the same and line goest 1-5

the above stringer would work for that too

 

[SantaMufasa]

>>> is this what i should send to my dba

Yes.

>>> also there might be times where there are 5 lines that need to in one row...if the type is the same and line goest 1-5

Yes. The code will concatenate as many lines as there are for the same TYPE and LINE.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

I am not sure if this is the same thing, but I had a need to take multiple lines of description and concatenate into one sentance. I did this with the Oracle recursive query. The table had a modifier id, description and line number. Here is the query. Please ignore if this is not useful and misses the requirements.

/* Concatenate multiple child rows into 1 column */
/* In the example modifier_desc has many lines of description per modifier_id */
WITH MY_HIERARCHY AS
(
SELECT MODIFIER_ID, MODIFIER_DESC,
ROW_NUMBER () OVER (PARTITION BY MODIFIER_ID ORDER BY LINE) rn,
COUNT (*) OVER (PARTITION BY MODIFIER_ID) cnt
FROM clarity.CLARITY_MOD_DESC
)

SELECT MODIFIER_ID,
REPLACE (SYS_CONNECT_BY_PATH (MODIFIER_DESC, ']'), ']', ' ') full_description
FROM MY_HIERARCHY
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR MODIFIER_ID = MODIFIER_ID AND PRIOR rn = rn - 1
ORDER BY MODIFIER_ID