Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (10g/11g)
SQL Query [message #635961] |
Tue, 14 April 2015 02:23 |
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 #635968 is a reply to message #635961] |
Tue, 14 April 2015 02:39 |
|
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:32Michel Cadot wrote on Tue, 15 July 2014 12:28Michel Cadot wrote on Mon, 14 July 2014 15:21
John Watson wrote on Thu, 16 June 2011 13:09What 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 |
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 #635972 is a reply to message #635970] |
Tue, 14 April 2015 02:49 |
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 #636498 is a reply to message #636495] |
Mon, 27 April 2015 02:00 |
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 |
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 #636503 is a reply to message #636501] |
Mon, 27 April 2015 02:16 |
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.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:33:54 CDT 2024
|