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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: 2 days ago @ 10:37 PM
Points: 3,306, Visits: 2,351
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
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1406918
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse