Hi everyone,

i am having a problem with this query, plz anyone help me regarding this problem, i will be very thankful


Suppose we hav a table with two fields

- id and state

id is not unique means one to many relation in id and state


for e.g

1 Delhi
1 Haryana
1 Punjab
2 Maharashtra
2 Goa

....

now we want our result in following form

1 delhi,Haryana,Punjab
2 Maharashtra,Goa

how we can achhieve it in single query without using cursor???

thanks for reading and your consideration
all suggestions are welcome,

thanks & regards

ashok sharma

First, you have to create a user-defined function that will accept the ID as the parameter and return the comma-delimited states. Here's a link that may be able to help you:

http://www.sql-server-helper.com/tip...ed-output.aspx


SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

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

Hi,

thanks for your reply, but there is a little problem

if i use 5000 instead of max, its working, otherwise it is giving me error, plz check

Msg 170, Level 15, State 1, Procedure ListOf, Line 10
Line 10: Incorrect syntax near 'max'.
Msg 137, Level 15, State 2, Procedure ListOf, Line 13
Must declare the variable '@str'.
Msg 137, Level 15, State 2, Procedure ListOf, Line 17
Must declare the variable '@str'.

i could not found why is this error

thanks for your consideration

regards

ashok sharma

Replace max with a valid number there.

_________________________
- Vijay G
Strive for Perfection