How group by works !

Question

1.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

SQL

SELECT COURSEID, COURSENAME, COURSEGROUP
FROM SOME_VIEW
WHERE STUDENTID=:STU_ID
--GROUP BY COURSEID, COURSENAME
ORDER BY COURSEID

I want the previous code to work like the following
if we have 2 data Rows

COURSEID-------COURSENAME------COURSEGROUP

PHY144----------physics------------------A

PHY144----------physics------------------B

What I really need is

COURSEID-------COURSENAME------COURSEGROUP

PHY144----------physics------------------A, B

please help me

Posted 19-Mar-12 22:50pm

kareem salem

Updated 19-Mar-12 22:53pm

v2

Add a Solution

2 solutions

Solution 1

hi,
try this,

SQL

SELECT COURSEID, COURSENAME, COURSEGROUP
FROM SOME_VIEW
GROUP BY COURSEID, COURSENAME
HAVING STUDENTID=STU_ID

Posted 19-Mar-12 22:56pm

Hrushikesh_phapale

Updated 19-Mar-12 23:14pm

v2

Comments

kareem salem 20-Mar-12 5:13am

hi Hrushikeshphapale,
thank you for your fast replyin
I tried this and It doesn't work
I get this error
ORA-00979: not a GROUP BY expression

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Saral S Stalin · Accepted Answer · 2012-03-19T23:12:00

Solution 2

You will have to write a sub query. You can not have a comma separated aggregate using group by alone. You can find an example here http://stackoverflow.com/questions/7448734/sql-comma-separated-row-with-group-by-clause[^]

Posted 19-Mar-12 23:12pm

Saral S Stalin

Comments

kareem salem 20-Mar-12 8:12am

thanks for your time
your link helped my somehow but the magic function wm_concat solved it

SELECT COURSEID, COURSENAME, WM_CONCAT(COURSEGROUP)
WHERE STUDENTID=:STU_ID
GROUP BY COURSEID, COURSENAME

Saral S Stalin 20-Mar-12 9:23am

Hmm...There is no equvalent of WM_CONCAT in SQL Server. So we are stuck with a subquery in the select

kareem salem 21-Mar-12 4:58am

I think there's something I get during my search like "FOR XML PATH" in SQL server. and Isn't supported by Oracle. I don't know more info about it