SQL Query Help Needed

foxbymariuss · June 19th, 2009, 01:57 AM

Hello,

I am kinda newbie to the SQL so please excuse if the question is dumb, but I don't know hot to figure it out.
I am working with an Oracle database (11g).
It all comes down to this:
I have a table that is:

column1 varchar2(10)
column2 number(5,2)
column3 varchar2(5)

I have to create a query that will group results by column1 (easy), sums the column2 values for identical column1 values and (here's the tricky part) to concatenate the texts in column3.
Something like:
column1 column2 column3
a__________1_________x
b__________2_________y
c__________3_________z
a__________15________w

I need the report to be something like:
column1 column2 column3
a_______1+15(16)____xw

select column1, sum(column2), column3 from table group by column1; -> will not work due to the restriction regarding the group by clause and aggregation functions. Using a subquery doesn't seem to do it, as I cannot concatenate the values in different rows in column3.

Does anyone know how this can be achieved?

Thanks a lot.

Old Pedant · June 19th, 2009, 07:16 PM

I don't use Oracle, but in SQL Server you can use COALESCE( ) in a user defined function to do this.

Try googling for "delimited list via sql" or "oracle delimited list via sql" and see if you don't get some hits. (Without "oracle" in there, you'll get back results for SQL Server, I know.)

peace95 · June 21st, 2009, 04:39 AM

foxbymariuss:
Normally in cases such as the one posted, I usually load the table into MS Access and manipulate the table information. However my suggestion is as follows:

Code:

DECLARE
 v_col1in col1val%TYPE;
 v_col2in col2val%TYPE;
 v_col3in col3val%TYPE;
 v_col1cnt PLS_INTEGER := 0;
 v_col2sum PLS_INTEGER :=0;
 v_col3concat VARCHAR2(20) := v_col3in;
 
CURSOR colmix_cur IS
  SELECT count(a.col1in), sum(a.col2in), concat(a.col3in)
  FROM intbl1 a
  WHERE 1=1
  GROUP BY a.col1in
  HAVING count(a.col1in) > 0
  ORDER BY a.col1in;
 
BEGIN
   DBMS_OUTPUT.ENABLE(500);
   OPEN colmix_cur;
   LOOP
        FETCH colmix_cur INTO v_col1cnt, v_col2sum, v_vol3concat;
        EXIT WHEN colmix_cur%NOTFOUND;
 
        DBMS_OUTPUT.PUT_LINE (v_col1in || ','  || v_col2sum || ',' || v_vol3concat);
  END LOOP;
  CLOSE colmix_cur;
END;

This is just a suggestion. I hope it helps.

foxbymariuss · June 22nd, 2009, 02:30 AM

Thanks a lot

I will first try doing the processing in the vb.net, as I don't find how to do it with sql.

I am grateful for your help.