[notice88]

Iwant to display all GroupNames from Groupsharing with Groupmembers coming from the group member table. I need the members to be separated by comma.

select (
	 declare @result varchar(500)
         set @result = ''
			select @result = @result + gm.Members + ', ' 
			from GroupMember gm  where gm.GroupID =gs.GroupID 
         select @result as GroupMembers
	) 
	as GroupMembers, GroupName from GroupSharing gs

ERROR:

[Err] 42000 - [SQL Server]Incorrect syntax near the keyword 'declare'.
42000 - [SQL Server]Incorrect syntax near ')'.

Attached image(s)

• 
• 

[BBeck]

So, probably the most appropriate way to write the query is something like this:

SELECT A. GroupName, B.Members
	FROM GroupSharing AS A
	LEFT OUTER JOIN GroupMember AS B
		ON A.GroupID = B.GroupID
        GROUP BY A.GroupID, B.Members
        ORDER BY A.GroupID, B.Members

(I did the left outer join to make sure you get all of the Groups from GroupMember, even if they have no Members.)

That's not "exactly" the answer you're asking for because it gives a denormalized result set where the GroupName is repeated over and over. Still, it's probably the most appropriate answer. I believe all the information that you want is there; it's more a problem with how it's formatted than what information is returned by that query. But I would strongly question the need to ever return the data in any other form.

But let's say you're doing a report here that goes to managers and you want to pretty it up for them. I "believe" what you are asking for is a correlated subquery. I really need to be working with your database to make sure I write it correctly, because it's still a bit of trial and error for me.

And I think it changed starting with SQL 2005. Before that, I'm not sure if you can do it without a cursor (you could use a cursor to build the string for one group at a time for all groups). In 2005 and later, I think you use that XML Path trick. I don't have time to look at it further right now (I've spent a little over an hour trying to figure out how to do it without the XML Path trick but need to go), but you might try Googling something like "t-sql convert vertical to horizontal".

I keep thinking that I've done this before with a correlated subquery. But the more I look at it, the more I'm not sure. I'm often working on SQL 2000 and can't use the XML Patht trick. So, I was trying to figure out if there's another way, but I'm not seeing it without building a cursor.

[notice88]

Column 'GroupSharing.GroupName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

thats the error i have encountered. but its fine, i got some ideas from what you have suggested. Thanks! maybe it will depend on my script on how will i display the result.. Many thanks!

[Ionut]

notice88, on 18 August 2012 - 07:51 AM, said:

Column 'GroupSharing.GroupName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This error wants to say that you have to add all the displaying columns in the group by or add an aggragate function to it.
Examples:

select sum(a), b, c, d 
from Table
Group By b, c --> error Column d is invalid in the select list because is not contained blah blah

select sum(a), b, c, d
from Table
Group By b, c, d -- no error

select sum(a), b, c, max(d)
from Table
Group by b, c --no error

Disclaimer : This examples are used just to illustrate an idea.

[BBeck]

Ionut is right. I was testing on my own database with totally different column names. And then I was trying to write basically the same thing using your object names. Anyway, GroupName would have to be in the GroupBy statement, like Ionut said. Maybe this might work:

SELECT A. GroupName, B.Members
	FROM GroupSharing AS A
	LEFT OUTER JOIN GroupMember AS B
		ON A.GroupID = B.GroupID
        GROUP BY A.GroupID, A.GroupName, B.Members
        ORDER BY A.GroupID, B.Members

I'm not sure if it has to also be included in the Order By statement or not. I'm not in front of a copy of SQL Server where I can test it. Usually, I just throw out some code like this and then work through any errors I get until it works the way I expect it to.

Actually, now that I look at it, I'm not sure you really need GroupID in the Group By or the Order By. It's really just needed for the Join. Maybe this:

SELECT A. GroupName, B.Members
	FROM GroupSharing AS A
	LEFT OUTER JOIN GroupMember AS B
		ON A.GroupID = B.GroupID
        GROUP BY A.GroupName, B.Members
        ORDER BY A.GroupName, B.Members

[e_i_pi]

As BBeck said earlier, building a cursor is the sure-fire way to achieve this in older versions of SQL Server, but if you're running 2005 or later, you can use a combination of FOR XML, generated XML, and STUFF to achieve the result you are after.

I'll post the code first, then give you a run-down of how it works:

SELECT
	STUFF(
		(
			SELECT 
				',' + CONVERT(nvarchar(32), gm.Members)
			FROM GroupMembers gm
			WHERE gm.GroupID = gs.GroupID
			FOR XML PATH(''), TYPE
		).value('.','VARCHAR(max)'),
		1,
		1,
		''
	) AS GroupMembers,
	GroupName
FROM GroupSharing gs

Let's look at this query from the inside out. The first thing we're doing here is creating some XML using this query:

SELECT
	',' + CONVERT(nvarchar(32), gm.Members)
FROM GroupMembers gm
WHERE gm.GroupID = 'e1b78'
--WHERE gm.GroupID = gs.GroupID
FOR XML PATH(''), TYPE

Note that I've replaced the WHERE clause, so that you can see the results for yourself (which may help with debugging for example). With your table data, that query will give the following XML:

,1001,95948,292919

Something you should note about this XML, it is not in the form you may be used to, since there are no nodes! This is completely fine, it just means that there is an empty root node, and your value is nestled in there.

You can read about the FOR XML clause here.

Next, let's look at the strange .value() function I've used:

(
	SELECT 
		',' + CONVERT(nvarchar(32), gm.Members)
	FROM GroupMembers gm
	WHERE gm.GroupID = gs.GroupID
	FOR XML PATH(''), TYPE
).value('.','NVARCHAR(max)')

The .value() function is a method that can be used on an XML object. The first parameter passed to this method is the node you are accessing. In this case, since we are accessing the empty root node, we use the parameter value '.', which is the character respresentation of the empty root node. The second parameter for the function is the SQL type that the value should be returned as, and in our case we want to return it as 'NVARCHAR(max)'. We return it as this type so that we can continue working with the value using SQL functions, rather than XML functions.

You can read about xml.value() here.

Lastly, let's look at the STUFF clause, which I will be the first to say, is the ugliest name for a clause that there could possibly be. The STUFF clause is essentially a string replace function. Because the data we have returned looks like this:

,1001,95948,292919

...and we want it to look like this...

1001,95948,292919

...we have to replace the leading character (,) with an empty string. The STUFF clause takes four parameters -

• The string you are working with
  
• The start position of the substring you will be replacing
  
• The length of the substring you will be replacing
  
• The string you will use to replace the substring

So, we are...

• Using the comma-separated value we have generated already
  
• ...starting at the first character
  
• ...for a length of one character
  
• ...replace with '' (i.e. - an empty string)

You can read about the STUFF clause here.

HTH

This post has been edited by e_i_pi: 23 August 2012 - 11:30 PM