I have prepared the following sql commands in order to solve this problem. First of all I created a table to include sample data. Later I prepared a udf that I'll use in the final sql statement.
Here is the sql commands to simulate the problem.
create table t (
insert into t select 1, 'Delhi'
insert into t select 1, 'Haryana'
insert into t select 1, 'Punjab'
insert into t select 2, 'Maharashtra'
insert into t select 2, 'Goa'
And the User defined function is as follows;
CREATE FUNCTION ListOf (
DECLARE @str AS nvarchar(max)
@str = COALESCE(@str + ', ', '') + description
FROM t (NoLock)
WHERE Id = @Id
And the final script that I can suggest is as follows:
SELECT distinct id FROM t
I hope this will help you
You can find more information on COALESCE usage for similar applications at http://www.kodyaz.com/forums/thread/76.aspx
and at http://www.kodyaz.com/articles/artic...x?articleid=29