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 March 21st, 2007, 12:17 AM
Friend of Wrox
 
Join Date: Aug 2006
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......
 
Old March 21st, 2007, 01:56 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 21st, 2007, 08:31 AM
Friend of Wrox
 
Join Date: Aug 2006
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......
 
Old March 21st, 2007, 08:35 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 21st, 2007, 08:38 AM
Authorized User
 
Join Date: Dec 2006
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
 
Old March 21st, 2007, 09:30 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 21st, 2007, 10:09 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 21st, 2007, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old March 21st, 2007, 10:33 AM
Friend of Wrox
 
Join Date: Jun 2003
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
======================================
 
Old March 21st, 2007, 10:50 AM
Friend of Wrox
 
Join Date: Jun 2003
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





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





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