Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (10g/11g)
SQL Query [message #635961] Tue, 14 April 2015 02:23 Go to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
I want to write the queries for the two following scenarios


Q.1

table1
==========

student_id GRADE_MATHS GRADE_PHY GRADE_CHEM

1 A
1 B
1 C
2 B
2 C
2 A


Write the QUERY FOR THE output as 1,A,B,C
2,B,C,A


o/p of each student is in a separate line:: The details are separated by a comma ','


Q.2

TABLE_2
=========

student_id grade_maths grade_phy grade_chem

1 A

1 B

1 C


2 C

2 null

2 C



o/p should be

student id grade_math grade_phy grade_chem

1 A B C

2 C null C


Please provide me with the queries.
Re: SQL Query [message #635965 is a reply to message #635961] Tue, 14 April 2015 02:25 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
We don't do homework.

Please read this http://www.orafaq.com/forum/t/88153/0/ amend your post accordingly with a test case and show us what you tried.
Re: SQL Query [message #635967 is a reply to message #635965] Tue, 14 April 2015 02:37 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
I applied DECODE function. Soon I will attach the details of workaround.



Re: SQL Query [message #635968 is a reply to message #635961] Tue, 14 April 2015 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lalit Kumar B wrote on Tue, 15 July 2014 17:32
Michel Cadot wrote on Tue, 15 July 2014 12:28
Michel Cadot wrote on Mon, 14 July 2014 15:21

John Watson wrote on Thu, 16 June 2011 13:09
What about saying "thank you"?


This means you have to review your previous topics.



For newbies who might not understand what Michel is trying to say :

This forum, (unlike other forums having point system to accredite the helpers with legends) consists of volunteers who doesn't care about the legend/rank. Giving a feedback is not only necessary to acknowledge the suggestion, but, it would complete the thread as well as it makes sure that the provided solution/suggestion really works practically.

Also, as Michel mentioned, reviewing previous topics is important since most of the posters who are amateurs tend to post a new topic which ultimately relates to the previous posts. I do understand that it might not be an easy task for newbies to understand the difference in the subsequent questions, considering the different levels of expertise of folks participating in the forums.

So, per whatever I kept blabbering above, all it needs is to come back with a feedback that constitutes a working test case which shows what you have implemented based on the suggestions.

Good luck!

NOTE : This is a off topic message. It bears no relevance to the technical aspects of this thread. Its only me who is responsible for the thoughts shared and folks who don't agree could PM me with their deferred thoughts and further suggestions. Sorry for an off topic post.


Regards,
Lalit


You do not follow the guide, you do not format your posts, you do not post a test case, you do neither feedback nor thank people who help you;
==> You do not deserve to be helped.

Re: SQL Query [message #635969 is a reply to message #635968] Tue, 14 April 2015 02:48 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
Hi all,

I tried with the following queries


select student_id,
SUM(DECODE(grade_maths, 'A', 'A','B','B','C','C', 0)) AS grade_maths,
SUM(DECODE(grade_phy, 'A', 'A','B','B','C','C', 0)) AS grade_phy,
SUM(DECODE(grade_chem, 'A', 'A','B','B','C','C', 0)) AS grade_chem
FROM student_det
GROUP BY student_id
ORDER BY student_id
/

==========

SQL> select student_id,
2 SUM(DECODE(grade_maths, 'A', 'A','B','B','C','C', 0)) AS grade_maths,
3 SUM(DECODE(grade_phy, 'A', 'A','B','B','C','C', 0)) AS grade_phy,
4 SUM(DECODE(grade_chem, 'A', 'A','B','B','C','C', 0)) AS grade_chem
5 FROM student_det
6 GROUP BY student_id
7 ORDER BY student_id;
SUM(DECODE(grade_maths, 'A', 'A','B','B','C','C', 0)) AS grade_maths,
*
ERROR at line 2:
ORA-01722: invalid number

select student_id,
SUM(DECODE(grade_maths, grade_maths, 0)) AS grade_maths,
SUM(DECODE(grade_phy, grade_phy, 0)) AS grade_phy,
SUM(DECODE(grade_chem, grade_chem, 0)) AS grade_chem
FROM student_det
GROUP BY student_id
ORDER BY student_id
/
==================



Re: SQL Query [message #635970 is a reply to message #635969] Tue, 14 April 2015 02:48 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
Thank You
Re: SQL Query [message #635971 is a reply to message #635969] Tue, 14 April 2015 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Tue, 14 April 2015 02:50]

Report message to a moderator

Re: SQL Query [message #635972 is a reply to message #635970] Tue, 14 April 2015 02:49 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
Results for the query tried

SQL> select student_id,
2 SUM(DECODE(grade_maths, grade_maths, 0)) AS grade_maths,
3 SUM(DECODE(grade_phy, grade_phy, 0)) AS grade_phy,
4 SUM(DECODE(grade_chem, grade_chem, 0)) AS grade_chem
5 FROM student_det
6 GROUP BY student_id
7 ORDER BY student_id;

STUDENT_ID GRADE_MATHS GRADE_PHY GRADE_CHEM
---------- ----------- ---------- ----------
1 0 0 0
2 0 0 0

Thanks
Ashish
Re: SQL Query [message #635973 is a reply to message #635972] Tue, 14 April 2015 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 14 April 2015 09:49

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: SQL Query [message #636495 is a reply to message #635973] Mon, 27 April 2015 00:06 Go to previous messageGo to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
hi

table_1
student grade
1 A
1 C
1 F
2 B
2 D
2 A

select student,wm_concat(grade) grades from table_1 group by student;

o/p
student grades
1 A,C,F
2 B,D,A

[Updated on: Mon, 27 April 2015 00:08]

Report message to a moderator

Re: SQL Query [message #636496 is a reply to message #636495] Mon, 27 April 2015 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

NO, not WM_CONCAT.

Read http://www.orafaq.com/forum/mv/msg/195357/636329/#msg_636329

Re: SQL Query [message #636498 is a reply to message #636495] Mon, 27 April 2015 02:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@saipavan.plsql,

It is goot to see that you are trying to help others. However, none of your posts show that you follow forum guidelines and you never use code tags. You will be appreciated if you be a bit more professional. Good luck!
Re: SQL Query [message #636500 is a reply to message #636496] Mon, 27 April 2015 02:02 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
Hi all,

The table is like this: I also attached previously the queries separately. The basic issue was that after pasting it was putting the data in one column

SQL> desc student_det;
Name Null? Type
----------------------------------------- -------- ------------------
STUDENT_ID NUMBER
GRADE_MATHS VARCHAR2(1)
GRADE_PHY VARCHAR2(1)
GRADE_CHEM VARCHAR2(1)

SQL> select * from student_det;

STUDENT_ID G G G
---------- - - -
1 A
1 B
1 C
2 A
2
2 C

6 rows selected.

Now I want the o/p as

1,A,B,C
2,A,null ,C


and for the other half

student id grade_math grade_phy grade_chem

1 A B C

2 A null C


Thanking everybody in advance. I again attached the document related to queries
Ashish

[Updated on: Mon, 27 April 2015 02:11]

Report message to a moderator

Re: SQL Query [message #636501 is a reply to message #636500] Mon, 27 April 2015 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 14 April 2015 09:50

Michel Cadot wrote on Tue, 14 April 2015 09:49

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.



SQL> desc student_det;
ERROR:
ORA-04043: object student_det does not exist

Re: SQL Query [message #636503 is a reply to message #636501] Mon, 27 April 2015 02:16 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
The table should be created as follows

create table student_det (student_id number, grade_maths varchar2(1),
grade_phy varchar2(1), grade_chem varchar2(1));

insert into student_det(student_id,grade_maths) values (1,'A');
insert into student_det(student_id,grade_phy) values (1,'B');
insert into student_det(student_id,grade_chem) values (1,'C');

insert into student_det(student_id,grade_maths) values (2,'A');
insert into student_det(student_id,grade_phy) values (2,'');
insert into student_det(student_id,grade_chem) values (2,'C');


The table structure is as follows:
SQL> desc student_det;
Name Null? Type
----------------------------------------- -------- -------------------
STUDENT_ID NUMBER
GRADE_MATHS VARCHAR2(1)
GRADE_PHY VARCHAR2(1)
GRADE_CHEM VARCHAR2(1)

SQL> select * from student_det;

STUDENT_ID G G G
---------- - - -
1 A
1 B
1 C
2 A
2
2 C

6 rows selected.
Re: SQL Query [message #636504 is a reply to message #636503] Mon, 27 April 2015 02:23 Go to previous message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 27 April 2015 09:06
Michel Cadot wrote on Tue, 14 April 2015 09:50

Michel Cadot wrote on Tue, 14 April 2015 09:49

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.




[Updated on: Mon, 27 April 2015 02:24]

Report message to a moderator

Previous Topic: Reading the Highvalue of partition and performing dynamic operation
Next Topic: While inserting record i got error ORA-00903: invalid table name
Goto Forum:
  


Current Time: Tue Apr 23 05:33:54 CDT 2024