Solved

Oracle returning multiple rows in one line

Posted on 2008-09-29
19
1,351 Views
Last Modified: 2013-12-18
I am trying to find a way to write a SQL statement that will allow me to do the following:

Field 1  Field 2  Field 3
-------  --------  --------
a         1           stuff1, stuff 2, stuff 3

Where "Field 3" is a result of another query.

I tried to write this:
Select Field1, Field2, (Select blah from x where x.item = a.item) as Field3
From a

I get the error code ora-01427 when I attempt this.  I do not know the size of the return set as it will vary depending on the query call.  I don't want to export this to an Excel spreadsheet, or create a table and dump the contents to it and then query it again.  Any and all help would be greatly appreciated.

Thanks in Advance,
James
0
Comment
Question by:vbemt
  • 11
  • 8
19 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598051
SELECT   field1,field2,
         LTRIM(EXTRACT(XMLAGG(XMLELEMENT("V", ',' || field3)),
                       '/V/text()'
                      ),
               ','
              ) AS STRING
    FROM yourtable
GROUP BY field1,field2;


or go to asktom.oracle.com  and search for STRAGG
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598075
if field3 comes from a different table then...

SELECT   a.field1, a.field2,
         LTRIM(EXTRACT(XMLAGG(XMLELEMENT("V", ',' || x.field3)),
                       '/V/text()'
                      ),
               ','
              ) AS STRING
    FROM yourtable a, yourothertable x
    WHERE a. item = x.item
GROUP BY a.field1, a.field2;

I do highly recommend getting stragg and installing it.
Makes these types of queries ever so much easier.  It's also very efficient.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598105
There is also a hierarchical way to do it too...

SELECT    field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_PATH(table_name, ',')  field3
      FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
                   ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
              FROM FROM yourtable a, yourothertable x
                 WHERE a. item = x.item
             )
START WITH curr = 1
CONNECT BY PRIOR curr = prev
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598110
oops, accidentally deleted a few characters...

SELECT    field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_PATH(table_name, ','),2))  field3
      FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
                   ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
              FROM FROM yourtable a, yourothertable x
                 WHERE a. item = x.item
             )
START WITH curr = 1
CONNECT BY PRIOR curr = prev
0
 

Author Comment

by:vbemt
ID: 22598141
I've seen comments about Stragg before.  I'm trying to find the exact function for it.  When I do find it, I execute it so that it's on the database then I can just reference it like it's any other function in a SQL statement?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598229
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

It's in pieces.  You have to create an object type and body first, then create the function to use the type.
0
 

Author Comment

by:vbemt
ID: 22598560
Where this all stems from is a co-worker of mine, used .net 3.5 and Sql Server to create the output.  I noticed that it used the following query to output the data.  So I am trying to find the "Oracle" way of doing it.  Is Stragg the only way?

select distinct c.TeamID,
      e.equipmentid,
      e.name24[Name],
      e.Equipment,
      '|' +
            (
                  select distinct rtrim(ltrim(cast(f2.FunctionID as nvarchar(20)))) + '|'
                  from equipment e2 inner join param p2 on e2.equpimentid = p2.equipmentid
                  inner join [function] f2 on p2.functionid = f2.functionid
                  where e2.equipment = e.equipment
                  for xml path('')) as [FunctionID]
                        ,(select distinct  rtrim(ltrim([code])) + ', '
                  from equipment e1
                        inner join param p1 on e1.equipmentid = p1.equipmentid
                        inner join [function] f1 on p1.functionid = f1.functionid
                  where e1.equipment = e.equipment
                  for xml path('')
            ) as [FC]
from Equipment e left join Points p on e.equipmentid = p.equipmentid
      left join TeamAssoc t on e.equpimentid = t.equipmentid
      left join Team c on t.Players = c.Players
group by c.Teamid, e.Equpmentid, e.name24, e.Equipment
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22598576
no, stragg isn't the only way,  I posted 2 other methods above.

stragg makes it easier though...

your query would simply be....

SELECT   a.field1, a.field2, stragg(x.field3)
 FROM yourtable a, yourothertable x
    WHERE a. item = x.item
GROUP BY a.field1, a.field2;
0
 

Author Comment

by:vbemt
ID: 22598620
Gotcha, I'm just trying to parse thru all the comments on STRAGG and figure out what I need to pull out and how to implement it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22598688
CREATE OR REPLACE TYPE string_agg_type
AS
    OBJECT(total varchar2(4000),
           STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
               RETURN number,
           MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
               RETURN number,
           MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
           flags IN number)
               RETURN number,
           MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
               RETURN number);

CREATE OR REPLACE TYPE BODY string_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN number
    IS
    BEGIN
        sctx   := string_agg_type(NULL);
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ',' || VALUE;
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
    flags IN number)
        RETURN number
    IS
    BEGIN
        returnvalue   := LTRIM(self.total, ',');
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ctx2.total;
        RETURN odciconst.success;
    END;
END;

CREATE OR REPLACE FUNCTION stragg(input varchar2)
    RETURN varchar2
    PARALLEL_ENABLE
    AGGREGATE USING string_agg_type;
0
 

Author Comment

by:vbemt
ID: 22598848
Thanks for the code, got it to work (kind of), now to remove dupilicates and also to sort that string result.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22599009
SELECT   DISTINCT
         field1,
         field2,
         stragg(field3)
             OVER (PARTITION BY field1, field2
                   ORDER BY field3
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  FROM   (SELECT   DISTINCT a.field1, a.field2, x.field3
            FROM   yourtable a, yourothertable x
           WHERE   a.item = x.item)
0
 

Author Comment

by:vbemt
ID: 22599600
Okay that works, one last thing (that I know of):
How can I alias that column.  Right now it appears as
field1 field2 stragg(field3) OVER (PARTITION BY field1, field2  ORDER BY field3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22599668
just put the alias after the expression....


SELECT   DISTINCT

         field1,

         field2,

         stragg(field3)

             OVER (PARTITION BY field1, field2

                   ORDER BY field3

                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) your_alias

  FROM   (SELECT   DISTINCT a.field1, a.field2, x.field3

            FROM   yourtable a, yourothertable x

           WHERE   a.item = x.item)

Open in new window

0
 

Author Comment

by:vbemt
ID: 22599793
Sometimes I feel stupid..... I put my orignal alias in the wrong place.  Thaks for all of your help.
0
 

Author Closing Comment

by:vbemt
ID: 31501244
It wasn't your fault on east to understand as much as it was Oracle's way of doing things "differently".
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22599812
don't worry about,  we all make mistakes, and if that's as bad as it gets, you're way ahead of the rest of us!

:)

I see this was your first question,  welcome to Experts-Exchange!
0
 

Author Comment

by:vbemt
ID: 22627118
Thanks for the help before, I was wondering if you can answer one more question about STRAGG.  Will it work if it's on just one table?

Example

Select x, y, stragg(z)
From my_table

Or do I need to do something special for it.  I am getting an ORA-00937: not a single-group group function when I attempt this.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22627942
stragg is just like any other aggregate function, you need to have group by.
or you may use it as an analytic too

thing about it like SUM for strings.

Select x, y, stragg(z)
From my_table
group by x,y




0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

867 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

18 Experts available now in Live!

Get 1:1 Help Now