Combining Multiple Cell Values into Single Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combining Multiple Cell Values into Single Column

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

Posted - 2010-06-18 : 05:23:59

quote:
Originally posted by senthil_nagore

Can you post how it will be in 2000?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

You need to use a function that concatenates values and call that function in the select statement. Or you can use quiky update menthod as shown below
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-18 : 05:31:54

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Can you post how it will be in 2000?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

You need to use a function that concatenates values and call that function in the select statement. Or you can use quiky update menthod as shown below
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Link was much helpful,

Thanks Madhi.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources