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 | 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 March 21st, 2007, 12:17 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default comma separated list

hi every body
plz solve my problem
i have a table called userarea which have 2 columns one is name and other is userarea.
for every name there are more than entry in userarea.
i want to write a query which give username and all its belonging areas in comma separated list as folllows;

name userarea
munish .net,ado.net,sql server


waiting for reply
regards
munish bhatia


thanks......
__________________
thanks......
Reply With Quote
  #2 (permalink)  
Old March 21st, 2007, 01:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

How do you want it in the result set which you didn't explain in your post?

Does this alone do?
Code:
select Name, UserArea from UserArea
Cheers

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old March 21st, 2007, 08:31 AM
Friend of Wrox
 
Join Date: Aug 2006
Location: Bangalore, Karnataka, India.
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default

i want the result set as explained in example

name userarea
munish a1,a2,a3
rahul b1,b2,b3

reply
regards
munish

thanks......
Reply With Quote
  #4 (permalink)  
Old March 21st, 2007, 08:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

How are the rows stored in the table right now?

name userarea
munish a1
munish a2
munish a3
rahul b1
rahul b2
rahul b3

Like this????

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old March 21st, 2007, 08:38 AM
Authorized User
 
Join Date: Dec 2006
Location: Lake Wylie, SC, USA.
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I feel like trying to read minds today...since I have a handy magic 8 ball.

Assuming the table contains many [userarea]s for a given [name]...
Would this get it done?

<script>
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @table TABLE (Nam varchar(10), UserArea varchar(10))
INSERT INTO @table SELECT 'munish', '.net'
INSERT INTO @table SELECT 'munish', 'ado.net'
INSERT INTO @table SELECT 'munish', 'sql server'

DECLARE @strName varchar(10), @strUserArea varchar(10), @strOutput varchar(1000)
SET @strName = 'munish'
DECLARE cursor_ CURSOR
FOR SELECT Nam, UserArea FROM @table WHERE Nam = @strName
OPEN cursor_
FETCH NEXT FROM cursor_ INTO @strName, @strUserArea
WHILE (@@fetch_status = 0)
BEGIN
    IF (@strOutput IS NOT NULL) SET @strOutput = @strOutput + ','
    SET @strOutput = @strOutput + @strUserArea
    FETCH NEXT FROM cursor_ INTO @strName, @strUserArea
END
CLOSE cursor_
DEALLOCATE cursor_

SELECT @strName
SELECT @strOutput
</script>

OUTPUT
munish
.net,ado.net,sql server

Adam Gossage
Lake Wylie, SC, USA
Reply With Quote
  #6 (permalink)  
Old March 21st, 2007, 09:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Assuming the data is stored in the table as mentioned in my previous post, try this...
Code:
DECLARE @UserArea varchar(255)
Select @UserArea = IsNull(UserArea + ',' + @UserArea, NULLIF( UserArea, @UserArea )) from test where Name = 'munish'
Select @UserArea
Cheers

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old March 21st, 2007, 10:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by happygv
 Assuming the data is stored in the table as mentioned in my previous post, try this...
Code:
DECLARE @UserArea varchar(255)
Code:
Select @UserArea = IsNull(UserArea + ',' + @UserArea, NULLIF( UserArea, @UserArea )) from test where Name = 'munish'
Select @UserArea
Cheers
This is "officially" undocumented behavior in SQL Server.

You cannot guarantee the order that the individual strings are returned in the query. The fact that it looks like it works most (all?) of the time you run it is no guarantee it will continue to work. Different servers, different caching situations, different software versions, different phases of the moon, etc. will all conspire to someday return the strings in a different order than whatever order it's doing today...

Note that an ORDER BY clause won't help, as the ordering on the resultset happens after the SELECT statement finishes executing.

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
Reply With Quote
  #8 (permalink)  
Old March 21st, 2007, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes, agreed. When ORDER BY is mandatory, this wouldn't be a reliable option. Thanks for put it across Jeff.

Cheers.

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #9 (permalink)  
Old March 21st, 2007, 10:33 AM
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Official or not, this is interesting, can you explain Vijay, I cant follow the query.

======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================
Reply With Quote
  #10 (permalink)  
Old March 21st, 2007, 10:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

It comes in a different order based on the order the query returns values and the way the comma separated string is constructed. To that extent one could have control over the order of the comma separated string that is constructed using this method.

NULLIF Returns NULL value if both the expressions are equal. First time the Variable is NULL, but the UserArea has a value, so that is returned and assigned to @UserArea Variable.
Code:
NULLIF( UserArea, @UserArea )
ISNULL replaces NULL with the specified replacement value.
Code:
IsNull(UserArea + ',' + @UserArea, NULLIF(...))
First time, UserArea + ',' + @UserArea returns NULL, since @UserArea is NULL initially, so the NULLIF returned value is stored into @UserArea, Next time onwards, the UserArea column values are appended to the @UserArea variable separated by Comma in the order it is picked. So the ORDER of constructing the string is not in our control if one wants it in a specific order.

Why I posted it is?
1) I assumed ORDER BY is not a contraint here.
2) I used it in many of my accomplishments, where I constructed EMAILIDs with ';' as delimiter.
3) Thought this could be useful to the original poster, if he tried to avoid cursor.

Hope that explains rodmcleay...
Cheers.

_________________________
- 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
Populate a list box from comma separated values CoderNH Access VBA 3 October 20th, 2008 08:37 AM
Comma Separated values aldwinenriquez SQL Server 2005 3 June 1st, 2007 08:52 PM
Comma Separated Column Value thru T-SQL subhendude SQL Server 2000 2 December 12th, 2006 01:42 PM
selecting elements from comma separated list rjonk XSLT 4 September 29th, 2006 08:46 AM
comma separated list of attributes rjonk XSLT 3 September 27th, 2006 11:52 AM



All times are GMT -4. The time now is 06:25 AM.


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