Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 23rd, 2007, 08:29 AM
Authorized User
 
Join Date: Feb 2006
Location: jaipur, rajasthan, India.
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


Reply With Quote
  #2 (permalink)  
Old May 23rd, 2007, 11:33 AM
Authorized User
 
Join Date: Oct 2005
Location: , , .
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
Reply With Quote
  #3 (permalink)  
Old May 23rd, 2007, 04:34 PM
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
  #4 (permalink)  
Old May 25th, 2007, 01:10 AM
Authorized User
 
Join Date: Feb 2006
Location: jaipur, rajasthan, India.
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
Reply With Quote
  #5 (permalink)  
Old May 28th, 2007, 05:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:08 AM.


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