| Author |
Topic |
|
vidhya.smarty
Starting Member
32 Posts |
 Posted - 2010-06-18 : 00:12:30
|
Dear Folks,
I've a scenario like the below, Please give me some idea to solve the Mystery
Say I've a table named Student which contains Stud ID and Stud Name
StudentID StudentName
1 xxxxx
2 yyyyy
And in the next table i've marks of the students stored in it
Stud ID StudentMarks
1 23
1 25
1 27
1 99
2 67
2 54
2 44
2 12
And i need the result as
Stud ID StudentMarks
1 23,25,27,99
2 67,54,44,12
Like the above shown result i need the values of the table
Student Marks should be displayed in a single cell by comma Separated..
Can you please help me to solve this 
Vidhu |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-18 : 02:05:48
|
Search for rowset concatenation+SQL Server in google/bing
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
vidhya.smarty
Starting Member
32 Posts |
Posted - 2010-06-18 : 02:39:07
|
Thanks for the Replay.. i'll check out and let you know..
if you find answers please reply me
Vidhu |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-06-18 : 02:42:41
|
Create table #student (id int,mark int)
insert into #student values(1,23)
insert into #student values(1,25)
insert into #student values(1,27)
insert into #student values(1,99)
insert into #student values(2,23)
insert into #student values(2,25)
insert into #student values(2,27)
insert into #student values(2,99)
select id, (SELECT CAST (mark AS VARCHAR(5)) + ',' as [text()]
FROM #student a where a.id=b.id
order by id
FOR XML PATH('') ) as mark from #student b group by id
Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled
http://senthilnagore.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-18 : 03:31:59
|
quote:
Originally posted by senthil_nagore
Create table #student (id int,mark int)
insert into #student values(1,23)
insert into #student values(1,25)
insert into #student values(1,27)
insert into #student values(1,99)
insert into #student values(2,23)
insert into #student values(2,25)
insert into #student values(2,27)
insert into #student values(2,99)
select id, (SELECT CAST (mark AS VARCHAR(5)) + ',' as [text()]
FROM #student a where a.id=b.id
order by id
FOR XML PATH('') ) as mark from #student b group by id
Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled
http://senthilnagore.blogspot.com/
Note that the question is posted in 2000 forum
Madhivanan
Failing to plan is Planning to fail |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-06-18 : 05:16:42
|
Can you post how it will be in 2000?
Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled
http://senthilnagore.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
|
|
vidhya.smarty
Starting Member
32 Posts |
Posted - 2010-06-18 : 07:31:31
|
Thats correct that, the link Question posted was in 2000 Forum..
I'm Sorry its my mistake. Actually i was wanting it for SQL server 2008..
However Mr.Senthil Nagore Logic is working.. But wen we go for multilingual its not working. The values in the field is coming as ????????????
So, Do any one of you have a valid comment for this?
Please, help me out in rectifying this...
Vidhu |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-18 : 08:34:09
|
use CAST (mark AS NVARCHAR(5))
Madhivanan
Failing to plan is Planning to fail |
|
|
|
vidhya.smarty
Starting Member
32 Posts |
Posted - 2010-06-18 : 09:14:58
|
Thank you madhi... its working fine... with this there is another problem..at the end there will be a unwanted comma.. It wil be good if we remove that comma
Vidhu |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-06-18 : 09:22:14
|
Try this
It will be costly, better you try it in front end.
select id,substring( (SELECT CAST (mark AS VARCHAR(5)) + ',' as [text()]
FROM #student a where a.id=b.id order by id FOR XML PATH('') ),0,len((SELECT CAST (mark AS VARCHAR(5)) + ',' as [text()]
FROM #student a where a.id=b.id order by id FOR XML PATH('') ))) as mark
from #student b group by id
Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled
http://senthilnagore.blogspot.com/
|
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-18 : 09:28:51
|
quote:
Originally posted by vidhya.smarty
Thank you madhi... its working fine... with this there is another problem..at the end there will be a unwanted comma.. It wil be good if we remove that comma
Vidhu
Use the first example shown in
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254 |
|
|
|
vidhya.smarty
Starting Member
32 Posts |
Posted - 2010-06-18 : 09:59:36
|
thanks for the reply i'll check out this on monday and give you reply
Vidhu |
|
|
|
|
Combining Multiple Cell Values into Single Column