Group by Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by Question

Author

Topic

Miaan82
Starting Member

3 Posts

Posted - 2008-04-21 : 09:49:45

I have a pretty simple question I suppose, but after hours of searching after an answer and no luck with that, I hope that you guys can help me.

I am trying to create a simple view with just two columns, one internal id and one for companys. The problem is that there are multiple accounts for every internal id and if I do a "Group by" they end up on different rows (of course). I want to show distinct internal id's in one column and the companys in one column separated by a comma.

Example

Internal id Companies
----------- ------------------------------------
123456 AAAAA
234567 DDDDD, EEEEE, FFFFF
345678 GGGGG, HHHHH

I would really appreciate your help!

Maria

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:08:45

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

Miaan82
Starting Member

3 Posts

Posted - 2008-04-21 : 10:13:33

The problem is that there are about 200 000 companies so i don't think that the posted solution will work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:22:24

Why not?

You have to separate the "prepare sample" section with "show expected output".
The first section is just for preparing some sample data because we have no access to your environment, right?

What you need is the second section only, the "show expected output".
Just replace @Sample table name with the table name used in your environment.
You didn't tell us the name of the table, so yo justhave to do something for yourself.

-- Show the expected output
SELECT DISTINCT	s1.ID,
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

SELECT DISTINCT	s1.ID,
		STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM		@Sample AS s1
ORDER BY	s1.ID

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 11:33:59

[code]SELECT t.Internalid,LEFT(cl.companylist,LEN(cl.companylist)-1) AS Companies
FROM YourTable t
CROSS APPLY (SELECT Company + ',' AS [text()]
FROM YourTable
WHERE Internalid=t.Internalid
FOR XML PATH(''))cl(companylist)[/code]

Miaan82
Starting Member

3 Posts

Posted - 2008-04-22 : 07:23:33

Thank you for all your help. I am closer to a solution, but have one more question, how do i write the query if i have to join two tables, one table with the internal id and one table with the companies?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 07:44:21

Something similar to

SELECT	i.ID,
	STUFF((SELECT ',' + c.Name FROM Companies AS c WHERE c.CompanyID = i.CompanyID FOR XML PATH('')), 1, 1, '') AS Names
FROM	InternalIDs AS i

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources