asked on

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?

Thanks,
Shweta

ASKER CERTIFIED SOLUTION

Metanil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RCorfman

>>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);

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

SQL>

Shweeta

ASKER

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?

Regards,
Shweta