Data in One Row.. - SQL Server Forums

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

Transact-SQL (2000)

Data in One Row..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-29 : 08:41:32

Hello Everyone,

Is there any way i can get all the records in a particular columns in one row separted by the some special charactes.

for eg in my table i have following records
Col1
1
2
3
4
5

i want the out put like this..

NewCol1
1,2,3,4,5

Thanks in Advance

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 08:47:48

declare @sql varchar(100)
Set @sql=''
Select @sql=@sql+','+cast(col1 as varchar) from yourTable
Select substring(@sql,2,len(@sql)-1) as NewCol

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-29 : 08:53:16

Thanks Madhivanan for ur quick help .. :-)

Complicated things can be done by simple thinking

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-29 : 09:03:11

declare @sql varchar(100)
Select @sql=COALESCE(@sql + ',', '') + EmpNameE from Employee
select @sql

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 09:11:49

In this case you can use Isnull also

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-29 : 09:15:38

aha.. thatz better i guess.. but cant we get the result without using the variable..???. its just i was trying but couldnt do that so.. .. ?

Complicated things can be done by simple thinking

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-29 : 09:21:13

yaa u cannot do without variables as then where will you store the value then

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 09:21:43

No I think it is not possible

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-29 : 09:23:13

what a difference in time in replying

Kapil Arya