February 5, 2008 at 3:38 am
I have a requirement where for a given student I need to generate a comma separated field of the courses he/she is taking.
Input
Data Course
Student1 crs1
Student1 crs2
Output required is
Student1 crs1,crs2
Can I write this query without using functions.
February 5, 2008 at 4:06 am
rosemary (2/5/2008)
I have a requirement where for a given student I need to generate a comma separated field of the courses he/she is taking.Input
Data Course
Student1 crs1
Student1 crs2
Output required is
Student1 crs1,crs2
Can I write this query without using functions.
Under SQL Server 2000 you would need to use a function. However, on 2005 you could use SELECT FOR XML PATH in the following way:
SELECT t1.studentID
, STUFF(( SELECT DISTINCT TOP 100 PERCENT
',' + CAST(t2.Course AS NVARCHAR)
FROM mytable AS t2
WHERE t1.studentID = t2.studentID
ORDER BY ',' + CAST(t2.Course AS NVARCHAR)
FOR
XML PATH('')
), 1, 1, '')
FROM mytable AS t1
GROUP BY t1.studentID
Regards,
Andras
February 5, 2008 at 4:49 am
I need it in sql server 2000.
But thanks!!!
February 5, 2008 at 5:02 am
The easiest way for you to solve it yourself is to look up cursors and then just build up a string as you loop over the rows. I guess performance isn't your key criteria here so that'll be ok (ie it will operate in 0.5 seconds for a typical dataset rather than 0.1 seconds - whoopee 😀 )
February 5, 2008 at 5:08 am
On the contrary performance is the key criteria here. I guess the data I have provided is quite simplistic to assume that. I know how to get this done without cursors (use function) . But client requirements doesnt allow me to use functions. 🙁
February 5, 2008 at 5:17 am
Does the client allow you to use temp tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2008 at 5:33 am
Nope no temp tables. I guess will have to rely on the front end to get this done.
February 5, 2008 at 5:35 am
When you say no functions does that include system functions as well?
If system functions are allowed have a look at this article: Using COALESCE to Build Comma-Delimited String
February 5, 2008 at 5:48 am
The query used in the article is what I was planning to use in the function. My problem is that the client doesnt want to be database centric, though the current database is Sql Server 2000. As I said I guess I will have to rely on the front end to get this done at the risk of a big perfomance hit.
February 5, 2008 at 6:28 am
Tough client...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2008 at 10:34 am
Hi Andras
Its more usefull for me,
Cheers
Sujith
February 8, 2008 at 8:53 am
If you know all the potential course names you could play this game...
Use Tempdb
if object_id('dbo.#Course') is not Null Drop table dbo.#Course
Select 'John'[Student],'Math'[Course] into dbo.#Course
Union All Select 'Steve','Science'
Union All Select 'Don','Math'
Union All Select 'Don','English'
Union All Select 'John','English'
Union All Select 'Steve','History'
Select * from dbo.#Course
Select
Student,
Left([Course List],Len([Course List])-1)[Course List]
from
(
Select
Student,
Max(Case Course when 'Math' then 'Math,' else '' End)+
Max(Case Course when 'Science' then 'Science,' else '' End)+
Max(Case Course when 'English' then 'English,' else ''End)+
Max(Case Course when 'History' then 'History,' else ''End)[Course List]
from dbo.#Course
group by Student
) t
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply