Is it possible to select data from multiple rows for output in a single line?

Question

I have 2 databases. I want to write a query that will pull data from both and tie them into the same result.

user database:

id  username  group
1   steve     group1
2   joe       group1
3   tom       group2

data database:

id  userid  fieldname   fieldresult
1   2       phone       867-5309
2   2       address     123 elm st
3   1       address     666 park avenue

If I just want steve's address, I could write:

select user.username, data.fieldresult from user, data where user.id = data.userid and data.fieldname = 'address' and user.username = 'steve';

The result would be:

username    fieldresult
steve       666 park avenue

But what if I want all of the fieldresults for joe in a single row? Is that possible to do from the query itself, or do I have to handle that in code?

Basically, I'd like to see:

username    phone       address
joe         867-5309    123 elm st

Is this wishful thinking? I wouldn't even need "phone" and "address" as the headers, as long as I could get their values in the same result.

NOTE: I'm working with an existing database and this is how the information is currently stored.

EDIT: Also, I need to do this on a much larger basis. Instead of querying by the username, can I get this information in the same way for each user in an entire group.

By "two databases" do you mean "two tables" (in the same database and under the same owner/schema)? — user5683823
– user5683823, Commented Jul 22, 2016 at 19:22
@woodchipper - not exactly a duplicate of the linked question. The linked question didn't require a join, the one here does. And the linked question asked to pivot the same kind of information; the current question makes just a little more sense, the second table is EAV which wasn't the case in the linked question. — user5683823
– user5683823, Commented Jul 22, 2016 at 19:24

Alex H · Accepted Answer · 2016-07-22 19:12:14Z

1

Try

select      a.username,
            b.fieldresult as phone,
            c.fieldresult as address
from        #user a
left join   #data b on a.id=b.userid and b.fieldname='phone'
left join   #data c on a.id=c.userid and c.fieldname='address'
where       a.id = 2

edited Jul 22, 2016 at 19:12

answered Jul 22, 2016 at 16:21

Alex H

3102 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prashant Majhwar · Accepted Answer · 2016-07-22 16:47:08Z

0

You can try pivoting contact info in datadb table and then join it with user

select * from userdb..[user] u join
 (
 select userid,phone, address from 
 (
 select userid,fieldname,fieldresult from datadb..data  
 ) as a pivot 
 (
  max(fieldresult) for fieldname in (phone, address)
 ) piv ) as a
on a.userid = u.id

this would result in something as follows.

id  username    group   userid  phone   address
1   steve       group1      1   NULL     park aven
2   joe         group1      2   867-5309    123 elm st

answered Jul 22, 2016 at 16:47

Prashant Majhwar

1801 silver badge9 bronze badges

Comments

Sunanda Das · Accepted Answer · 2016-07-22 19:28:42Z

0

The two tables i used are user_ and data. Hope,this might help you

DECLARE
user_name user_.username%type:='joe';
phone_no data.fieldresult%type;
full_address data.fieldresult%type;
user_id user_.id%type;

CURSOR c_name IS select * from data;
result c_name%rowtype;

BEGIN

select id into user_id from user_ where username=user_name; 
open c_name;

LOOP

fetch c_name into result;

IF ( result.userid=user_id AND result.fieldname='phone') THEN
phone_no:=result.fieldresult;
END IF;  

IF(result.userid=user_id AND  result.fieldname='address') THEN
full_address:=result.fieldresult;
END IF;


EXIT when c_name%NOTFOUND;
END LOOP;
close c_name;

dbms_output.put_line(user_name||'       '||phone_no||'      '||full_address);

END;
/

OUTPUT: joe 867-5309 123 elm st

edited Jul 22, 2016 at 19:28

answered Jul 22, 2016 at 19:13

Sunanda Das

213 bronze badges

4 Comments

user5683823 Over a year ago

Why do you need to do this in a procedure? Definite de-merit for terrible idea.

Sunanda Das Over a year ago

Mainly i use CURSOR ,I actually don't know is CURSOR a PROCEDURE or not??? If it is then would you mind explain it ?? @mathguy

user5683823 Over a year ago

Oracle implicitly uses cursors to handle SQL statements. If you want to use cursors you must do that in PL/SQL (in a procedure or function). To restate my question: why do you need a cursor to solve the original problem? Prashant already posted the correct answer two hours before you posted yours. You seem to still be learning. What did your answer contribute to this thread? Please post only when you have a better, simpler, faster etc. solution; answering other people's questions is not the place to "guess" while you are still learning.

Sunanda Das Over a year ago

Thanks @mathguy for replying and for your valuable suggestions.

Collectives™ on Stack Overflow

Is it possible to select data from multiple rows for output in a single line?

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related