5 Replies - 961 Views - Last Post: 22 August 2012 - 09:50 PM

#1 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 87
  • Joined: 21-December 11

Select all Group in a table with all its members coming from other tbl

Posted 16 August 2012 - 08:22 PM

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)

  • Attached Image
  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Select all Group in a table with all its members coming from other tbl

#2 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 581
  • View blog
  • Posts: 1,290
  • Joined: 24-April 12

Re: Select all Group in a table with all its members coming from other tbl

Posted 17 August 2012 - 03:58 PM

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.
Was This Post Helpful? 1
  • +
  • -

#3 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 87
  • Joined: 21-December 11

Re: Select all Group in a table with all its members coming from other tbl

Posted 18 August 2012 - 12:51 AM

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!
Was This Post Helpful? 0
  • +
  • -

#4 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Select all Group in a table with all its members coming from other tbl

Posted 18 August 2012 - 03:20 AM

View Postnotice88, 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.
Was This Post Helpful? 0
  • +
  • -

#5 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 581
  • View blog
  • Posts: 1,290
  • Joined: 24-April 12

Re: Select all Group in a table with all its members coming from other tbl

Posted 19 August 2012 - 09:35 AM

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


Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Select all Group in a table with all its members coming from other tbl

Posted 22 August 2012 - 09:50 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1