Select query to get fields from different records into one field of output

Posted on 2006-03-22
Last Modified: 2009-12-16
I have a table

Field1    Field2      Field3      Field4
a          b             c              12
a          b             c              23
c          d             b              11
a         c              d              88
c         d              b              99

I want a query that would give me rows only for unique combinations of field1, field2 and field3. and for this combination, the fourht field will have values comma separated.

ie.. the output for the above sample data would be:

"a"  "b"  "c"  "12,23"
"c"  "d"  "b"  "11,99"
"a" "c"   "d"  "88"

Is this possible through sql queries only? Or, will I have to take help of stored proc and cursor in cursor logic?

Question by:Shweeta
    LVL 9

    Accepted Solution

    This one is called string aggregation:

    First create a function:
    CREATE OR REPLACE FUNCTION get_field (mt_field1  in  tbl1.field1%TYPE,mt_field2  in  tbl1.field2%TYPE,mt_field3  in  tbl1.field3%TYPE)
      l_text  VARCHAR2(32767) := NULL;
      FOR cur_rec IN (SELECT field4 FROM tbl1 WHERE field1 = mt_field1 AND field2 = mt_field2 AND field3 = mt_field3) LOOP
        l_text := l_text || ',' || cur_rec.field4;
      END LOOP;
      RETURN LTRIM(l_text, ',');

    You can then run your query:
    SELECT field1,field2,field3,
          get_field(field1,field2,field3) field4
            FROM   tbl1
          group by field1,field2,field3

    You can do this too:
    SELECT a.field1,a.field2,a.field3,
           get_field(a.field1,a.field2,a.field3) field4
    FROM   (SELECT DISTINCT field1,field2,field3
            FROM   tbl1) a;

    Note: Second one is fast.

    LVL 16

    Expert Comment

    >>Is this possible through sql queries only?
    Not to my knowledge. Normal SQL will not consolidate rows into one, it intersects, project, etc, but it doesn't combine.
    >>will I have to take help of stored proc and cursor in cursor logic?
    Yes, you need a stored proc.  A cursor in cursor (select of a subselect) is the best approach.

    I too would use the approach that Metanil used, but to get your quote seperated data, you need to run the queries a little differently.  First, create the function as Metanil showed. I also agree you should use his second query format, it is much better as it restricts the rows before it starts calling the function.

    But, if you really wanted the data strung together with " around the data seperated by spaces, you need the SELECT portion of the SQL to concatenate the fields together along with quotes.

    The query you really want to do that is:
    SELECT '"' ||
                field1 || '" "' ||
                field2 || '" "' ||
                field3 || '" "' ||
                get_field(field1,field2,field3) || '"'
                as quoted_data
    FROM   (SELECT DISTINCT field1,field2,field3
            FROM   tbl1);

    SQL> SELECT '"' ||
      2              field1 || '" "' ||
      3              field2 || '" "' ||
      4              field3 || '" "' ||
      5              get_field(field1,field2,field3) || '"'
      6              as quoted_data
      7  FROM   (SELECT DISTINCT field1,field2,field3
      8          FROM   tbl1);

    "a" "b" "c" "12,23"
    "a" "c" "d" "88"
    "c" "d" "b" "11,99"

    LVL 1

    Author Comment

    I have another question regarding this.

    I have written function with help from Metanil with some more variations.

    When I run the select query:
    SELECT a.field1,a.field2,a.field3,
           get_field(a.field1,a.field2,a.field3) field4
    FROM   (SELECT DISTINCT field1,field2,field3
            FROM   tbl1) a;

    it hangs after certain number of rows.. in our case 330. What could be the reason for this?

    Can I extend points in this? How do I do that?


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now