February 13, 2018 at 7:58 am
I cant run the query because of subquery returned more than 1 value due to coalesce. But, we need comma delimited list of account Id’s for each user.
Can anyone please help?
SELECT DISTINCT au.name, aau. firstname,aau.lastname,r.rolname,
COALESCE( (SELECT DISTINCT CAST(AccID AS VARCHAR(MAX))
FROM [Admin] AS A WITH(NOLOCK)
where a.Id = Au.UserId) + ',', '')
FROM admin_Users AS aU WITH(NOLOCK)
inner join active_users aau on au.name = aau.name
inner join roles r on r.roleid = au.roleid
February 13, 2018 at 8:32 am
Use XML concatentation.
SELECT au.name, aau. firstname,aau.lastname,r.rolname,
STUFF(
(
SELECT ',' + CAST(AccID AS VARCHAR(MAX))
FROM [Admin] AS A
WHERE a.Id = Au.UserId
ORDER BY AccID
FOR XML PATH('')
), 1, 1, '')
FROM admin_Users AS aU
inner join active_users aau on au.name = aau.name
inner join roles r on r.roleid = au.roleid
Why are you using the WITH(NOLOCK) hint? Do you really want to read dirty data?
Why are you using DISTINCT? I don't see anything that should produce duplicates if your database is properly designed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 13, 2018 at 9:13 am
drew.allen - Tuesday, February 13, 2018 8:32 AMUse XML concatentation.
SELECT au.name, aau. firstname,aau.lastname,r.rolname,STUFF(
(
SELECT ',' + CAST(AccID AS VARCHAR(MAX))
FROM [Admin] AS A
WHERE a.Id = Au.UserId
ORDER BY AccID
FOR XML PATH('')
), 1, 1, '')FROM admin_Users AS aU
inner join active_users aau on au.name = aau.name
inner join roles r on r.roleid = au.roleidWhy are you using the WITH(NOLOCK) hint? Do you really want to read dirty data?
Why are you using DISTINCT? I don't see anything that should produce duplicates if your database is properly designed.
Drew
Thanks for your quick response. We are fine with dirty read as we need as this is for testing purpose only.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply