Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

sql procedure Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 12:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
All you showed was the SELECT column list. Could you please provide the full query based on the test table I provided in an earlier post? I would like to see how you would get the grouping of the subject IDs for a particular student.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406903
Posted Monday, January 14, 2013 12:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
Now I see the trouble with what you were asking. Does this give you what you're after? It uses a correlated subquery [child] to concatenate the list for each row in [parent]. The [formatted] name is just for formatting it nicely and removing the trailing comma. Since there was no table name in the original post, I used a temporary table named [#test], but you can change it however you like.

SELECT formatted.grade, LEFT(formatted.grade_id_list, LEN(formatted.grade_id_list) - 1) 
FROM (SELECT DISTINCT parent.grade, (SELECT CONVERT (Varchar, child.id_grade) + ','
FROM #test child
WHERE child.grade = parent.grade
ORDER BY child.grade
FOR XML PATH ('')) grade_id_list
FROM #test parent) formatted
ORDER BY formatted.grade;

I have no idea if this will work in MySQL at all.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1406916
Posted Monday, January 14, 2013 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
Ed Wagner (1/14/2013)
Now I see the trouble with what you were asking. Does this give you what you're after? It uses a correlated subquery [child] to concatenate the list for each row in [parent]. The [formatted] name is just for formatting it nicely and removing the trailing comma. Since there was no table name in the original post, I used a temporary table named [#test], but you can change it however you like.

SELECT formatted.grade, LEFT(formatted.grade_id_list, LEN(formatted.grade_id_list) - 1) 
FROM (SELECT DISTINCT parent.grade, (SELECT CONVERT (Varchar, child.id_grade) + ','
FROM #test child
WHERE child.grade = parent.grade
ORDER BY child.grade
FOR XML PATH ('')) grade_id_list
FROM #test parent) formatted
ORDER BY formatted.grade;

I have no idea if this will work in MySQL at all.

Welcome! We are now on the same page.

That is the XML PATH technique I alluded to in my earlier post. It is equivalent to using the SQLCLR object I linked to which attempts to provide a replacement for MySQL's GROUP_CONCAT on SQL Server.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406918
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse