• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

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

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?

1 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;
  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.

>>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"

ShweetaAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now