How to...Query Help - 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)

How to...Query Help

Author

Topic

pupli
Starting Member

8 Posts

Posted - 2008-11-27 : 11:23:43

Hi everybody,

I need some with the results of a query.

I have this table

EMPLOYES

-------------------------
Name |Salary |Company |
-------------------------
Andrew | 1245 | ABC |
-------------------------
Mark | 1521 | ASD |
-------------------------
Mary | 5484 | EDC |
-------------------------
Andrew | 5124 | DEA |
-------------------------
John | 3521 | ASD |
-------------------------
John | 6124 | DEA |
-------------------------

I want my query to output the following result:

Name Salary Company
----- ------- --------
Andrew 6369 ABC + DEA
Mark 1521 ASD
Mary 5484 EDC
John 9645 ASD + DEA

How can I do this, with the "Company" field?
This field is of type string

------------------------
If I only could.....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 11:39:16

SELECT Name,sum(salary)as Salary,
STUFF((SELECT '+' + CONVERT(VARCHAR(22),Company) FROM table WHERE name = t.name FOR XML PATH('')),1,1,'')as Company
FROM table t
GROUP BY name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 11:47:51

[code]SELECT t.Name,t.Salary,LEFT(cl.CompList,LEN(cl.CompList)-1) AS Company
FROM (SELECT Name,SUM(Salary) AS Salary
FROM EMPLOYEES
GROUP BY Name) t
CROSS APPLY (SELECT Company+ '+'
FROM EMPLOYEES
WHERE Name=t.Name
FOR XML PATH(''))cl(CompList)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources