Please Help

I have table like this..

Column1 Column2

--

1 Murder & Nonnegligent Manslaughter

1 Negligent Manslaughter

1 Justifiable Homicide

1 Kidnaping/Abduction

How to display: Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide, Kidnaping/Abduction

Thanks

Malar:

See if this post, which discusses how to map multiple rows into a single column, provides some help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1262561&SiteID=1

|||

Here is a mockup:

declare @.sample table
( column1 integer,
column2 varchar(500)
)
insert into @.sample values (1, 'Murder & Nonnegligent Manslaughter')
insert into @.sample values (1, 'Negligent Manslaughter')
insert into @.sample values (1, 'Justifiable Homicide')
insert into @.sample values (1, 'Kidnaping/Abduction')
insert into @.sample values (2, 'Just a nominal entry')

select distinct column1,
left (replace (o.list, '~', '&'), len (o.list)-1)
as [List]
from @.sample a
cross apply
( select replace(column2,'&','~') + ',' as [text()]
from @.sample s
where a.column1 = s.column1
for xml path ('')
) o (list)
order by a.column1

-- column1 List
-- -- --
-- 1 Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide,Kidnaping/Abduction
-- 2 Just a nominal entry

|||Thanks for your reply. I am using SQL Server 2000. I cannot use for xml path (''). Is there any other way to solve this issue?|||Yes, there is; one way is to use a scalar function to return the needed data. I will get an example of this in a minute. In the meantime, I would like to recruit suggestions from other members.|||

Here is an example using a scalar UDF:

create table dbo.mockup
( column1 integer,
seq integer,
column2 varchar(500)
)
insert into mockup values (1, 1, 'Murder & Nonnegligent Manslaughter')
insert into mockup values (1, 2, 'Negligent Manslaughter')
insert into mockup values (1, 3, 'Justifiable Homicide')
insert into mockup values (1, 4, 'Kidnaping/Abduction')
insert into mockup values (2, 1, 'Just a nominal entry')
go

create function dbo.stringProduct
( @.prm_column1 integer
)
returns varchar(255)
as
begin

declare @.retValue varchar(255) set @.retValue = ''

select @.retValue = @.retValue
+ case when len(@.retValue) = 0 then ''
else ',' end
+ column2
from mockUp
where column1 = @.prm_column1
order by seq

return @.retValue

end

go

select distinct column1,
dbo.stringProduct (column1)
as [List]
from mockUp

-- column1 List
-- -- --
-- 1 Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide,Kidnaping/Abduction
-- 2 Just a nominal entry