Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 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
Edited 19-Mar-12 22:53pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Comments
kareem salem at 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 at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

hi,
try this,
 
 SELECT COURSEID, COURSENAME, COURSEGROUP
 FROM SOME_VIEW 
 GROUP BY COURSEID, COURSENAME
 HAVING STUDENTID=STU_ID
  Permalink  
v2
Comments
kareem salem at 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

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 20 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100