Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 23rd, 2007, 08:29 AM
Authorized User
 
Join Date: Feb 2006
Posts: 67
Thanks: 7
Thanked 0 Times in 0 Posts
Send a message via Yahoo to beetle_jaipur
Default 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


 
Old May 23rd, 2007, 11:33 AM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 23rd, 2007, 04:34 PM
Registered User
 
Join Date: Mar 2005
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
 
Old May 25th, 2007, 01:10 AM
Authorized User
 
Join Date: Feb 2006
Posts: 67
Thanks: 7
Thanked 0 Times in 0 Posts
Send a message via Yahoo to beetle_jaipur
Default

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
 
Old May 28th, 2007, 05:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM





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