Thread: query problem
View Single Post
  #3 (permalink)  
Old May 23rd, 2007, 04:34 PM
eralper eralper is offline
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

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 (
    id int,
    description varchar(15)
)
go
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'
go


And the User defined function is as follows;

CREATE FUNCTION ListOf (
 @Id int
)
RETURNS nvarchar(255)

AS

BEGIN

 DECLARE @str AS nvarchar(max)

 SELECT
  @str = COALESCE(@str + ', ', '') + description
 FROM t (NoLock)
 WHERE Id = @Id

 RETURN @str

END

GO


And the final script that I can suggest is as follows:

SELECT
    t.id, dbo.ListOf(t.id)
FROM (
    SELECT distinct id FROM t
) 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


Eralper



Eralper
http://www.kodyaz.com
Reply With Quote