Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL: Compare and gathering data

Posted on 2011-10-25
8
Medium Priority
?
230 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:MEINMEL
  • 3
  • 3
  • 2
8 Comments
 

Expert Comment

by:jeyakkumar
ID: 37026486
HI,
 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)
0
 

Expert Comment

by:jeyakkumar
ID: 37026524
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 .. :)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37026627
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 1

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

Author Comment

by:MEINMEL
ID: 37026692
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37026814
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
0
 

Author Comment

by:MEINMEL
ID: 37026965
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.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 37027099

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  (select distinct userid
         from  cad.UserAgencyRestriction) as uar
inner join adm.[User] u
on u.userid=uar.userid
     
ORDER by 1
0
 

Author Closing Comment

by:MEINMEL
ID: 37027149
YOU ROCK!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question