SQL: Compare and gathering data
I have a sql table that contains user names and the agencies they are restricted to:
UserName Agency
Dave T200
MMein 04042
Dave HHF001
LSand HCF200
I want to return data from that table looking like this:
Dave T200, HHF001
MMein 04042
LSand HCF200
So far my sproc looks like this. However, it brings back all of the agencies into one user name, like this:
Dave T200, HHF001, 04042, HCF200
What is the proper way to accomplish this? Thanks
DECLARE
@userID INT = 42
,@userName VARCHAR(25)
,@agencyList VARCHAR(100)
,@userAgencyList VARCHAR(100)
SET @userAgencyList = ''
SET @agencyList = ''
select
@userName = u.DisplayName,
@userAgencyList = @userAgencyList + a.Identifier + ' '
from cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
left join adm.Agency a
on a.AgencyID = uar.AgencyID
UserName Agency
Dave T200
MMein 04042
Dave HHF001
LSand HCF200
I want to return data from that table looking like this:
Dave T200, HHF001
MMein 04042
LSand HCF200
So far my sproc looks like this. However, it brings back all of the agencies into one user name, like this:
Dave T200, HHF001, 04042, HCF200
What is the proper way to accomplish this? Thanks
DECLARE
@userID INT = 42
,@userName VARCHAR(25)
,@agencyList VARCHAR(100)
,@userAgencyList VARCHAR(100)
SET @userAgencyList = ''
SET @agencyList = ''
select
@userName = u.DisplayName,
@userAgencyList = @userAgencyList + a.Identifier + ' '
from cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
left join adm.Agency a
on a.AgencyID = uar.AgencyID
Please try this in SQL Server
SELECT
distinct username
,NameList=STUFF((SELECT ','+agency FROM <tablename> WHERE username=A.username FOR XML PATH('')) , 1 , 1 , '' )
FROM
<tablename> A
order by 1 desc
Hope it may work .. :)
SELECT
distinct username
,NameList=STUFF((SELECT ','+agency FROM <tablename> WHERE username=A.username FOR XML PATH('')) , 1 , 1 , '' )
FROM
<tablename> A
order by 1 desc
Hope it may work .. :)
like this
select
u.DisplayName,
Stuff((
SELECT ' '+a.identifier
FROM adm.Agency a
WHERE a.AgencyID = uar.AgencyID
FOR XML PATH(”)),1,1,'') as Agencylist
from cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
order by 1ASKER
Wow, thanks for the responses!
This is what I've got now:
select
u.DisplayName,
STUFF((select ',' + a.identifier From adm.agency a where
a.AgencyID = uar.AgencyID FOR XML Path('')),1,1,'') as AgencyList
from
cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
ORDER by 1
However, it is returning each as a seperate row:
LSand 04042
Lsand T200
MMein MDF200
MMein HHF001
and I would like them like this:
LSand 04042 T200 (or comma instead of space)
MMein MDF200 HHF001
This is what I've got now:
select
u.DisplayName,
STUFF((select ',' + a.identifier From adm.agency a where
a.AgencyID = uar.AgencyID FOR XML Path('')),1,1,'') as AgencyList
from
cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
ORDER by 1
However, it is returning each as a seperate row:
LSand 04042
Lsand T200
MMein MDF200
MMein HHF001
and I would like them like this:
LSand 04042 T200 (or comma instead of space)
MMein MDF200 HHF001
select
u.DisplayName,
STUFF((select ',' + a.identifier
From cad.UserAgencyRestriction uar
inner join adm.agency a
on a.AgencyID = uar.AgencyID
where u.UserID = uar.UserID
FOR XML Path('')),1,1,'') as AgencyList
from
adm.[User] u
ORDER by 1
u.DisplayName,
STUFF((select ',' + a.identifier
From cad.UserAgencyRestriction uar
inner join adm.agency a
on a.AgencyID = uar.AgencyID
where u.UserID = uar.UserID
FOR XML Path('')),1,1,'') as AgencyList
from
adm.[User] u
ORDER by 1
ASKER
Getting closer. However, the returned list includes all DisplayNames from adm.User with AgencyList NULL for most - except the ones that actually have the restrictions - and those are displaying as I need them to.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YOU ROCK!
The below query will work for MySQL ..
SELECT username, group_concat(Agency)
as data FROM <tablename> group by username
Hope it will help you (MySQL it is very simple)