Trying to solve duplicated records with this query

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

General SQL Server Forums

New to SQL Server Programming

Trying to solve duplicated records with this query

Author

Topic

fasoliie
Starting Member

4 Posts

Posted - 2012-08-31 : 23:23:21

Hello,

This is an example of the database structure,

As we can see 1 person can handl more than 1 site, so when I select them using this query (it's long query but I mad it short for explaining)

Select AA, BB, Inc_Count
from
(
Select
ASSIGNEE As AA,
SITE As BB,        
Count(INCIDENT_NUMBER) as inc_count From tablename xxx 
where ASSIGNED_GROUP like 'groupname xxx'
Group By 
ASSIGNEE,
SITE,
)

this query will print Joseph and Micheal twice like
- Joseph Site 12
- Joseph Site 17

What I want to be like this
- Joseph Site 12 Site 17

So if you have a soultion I will be thankful

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 23:26:08

you want Site 12 & Site 17 in 2 separate column or single column with comma as separator ?

KH
[spoiler]Time is always against us[/spoiler]

fasoliie
Starting Member

4 Posts

Posted - 2012-08-31 : 23:49:44

khtan
I'm using this query in dashboard and I belive the dashboard will not understand if it's spreated with a comma
so separate column will be good

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 23:52:37

is the a maximum possible no of site per assignee ?

KH
[spoiler]Time is always against us[/spoiler]

fasoliie
Starting Member

4 Posts

Posted - 2012-09-01 : 01:05:54

a will show the assignee name and b will show the site that assgned to the assignee. 1 person could have more than one site

fasoliie
Starting Member

4 Posts

Posted - 2012-09-01 : 01:06:02

a will show the assignee name and b will show the site that assgned to the assignee. 1 person could have more than one site

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 01:45:24

;with cte(name,sie)
as
(
select name,stuff((select distinct
'/ ' + b.sie
from
dbo.site b
where
a.name = b.name
for xml path('')),1,1,'') as Transactions From dbo.site a

)
select distinct name,sie from cte

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources