p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   query problem (http://p2p.wrox.com/showthread.php?t=58243)

beetle_jaipur May 23rd, 2007 08:29 AM

query problem
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

sshelper May 23rd, 2007 11:33 AM

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:


SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!

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

eralper May 23rd, 2007 04:34 PM


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)
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;

 @Id int
RETURNS nvarchar(255)



 DECLARE @str AS nvarchar(max)

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

 RETURN @str



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

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



beetle_jaipur May 25th, 2007 01:10 AM


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


ashok sharma

happygv May 28th, 2007 05:07 AM


DECLARE @str AS nvarchar(max)
Replace max with a valid number there.

- Vijay G
Strive for Perfection

All times are GMT -4. The time now is 09:47 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.