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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-19 : 13:26:19

Hi again,

I have the following result when doing a query.
Field1 Operator JobID
-----------------------------
tpc4 Jim McGraw 4
tpc4 Joe Cutter 4
tpc4 Emploee 4

How can I adjust my query so that I get the following result

Field1 Operator JobID
-----------------------------
tpc4 Jim McGraw,Joe Cutter,Emploee 4

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 13:29:31

[code]SELECT Field1,
LEFT(ol.OperatorList,LEN(ol.OperatorList)-1) AS Operator,
JobID
FROM (SELECT DISTINCT Field1,JobID YourTable) t
CROSS APPLY (SELECT Operator+ ',' AS [text()]
FROM YourTable
WHERE Field1=t.Field1
AND JobID=t.JobID
FOR XML PATH(''))ol(OperatorList)[/code]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-19 : 15:51:23

WOW!!! Thank you excellent approach solves the problem PERFECTLY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources