CODEWITHSUNDEEP
sqlsql-serveraggregate-functionsdistinctstring-aggregation
BALA HARISH M
BALA HARISH M

Reputation: 21

Query to find distinct string to agregate

I have a database table named logoninfo. Below is the schema.

   Record ID|User ID | Computer ID |LOGON_TIME|LOGOFF_TIME

If the logoff time is 0 then the user is currently logged in the computer . The user name is in separate table named "user" with schema

   user id  | user name

The computer name is in separate table named "computer" with schema

    computer id | computer name

I need to have a report which has user name and its corresponding currently logged on computers (separated by comma)

        user name | computer names

I used the below query but it results in duplicate computers in some cases (same computer shown more than once).

select user.name as USER_NAME,
    userLogon."comps" as COMPUTERS
from user
    inner join (
        select user_id as "user_resource",
            string_agg(compRes.name, ' ,') as "comps"
        from logoninfo
            inner join computer compRes on logoninfo.computer_id = compRes.computer_id
        where logoninfo.logoff_time = 0
        group by logoninfo.user_id
    ) userLogon on userLogon."user_resource" = user.user_id

So I tried to add distinct to string_agg function, but it is not possible in SQL Server .

Please help me with query to the required report.

Thanks in advance.

Upvotes: 0

Views: 58

Answers (3)

GMB
GMB

Reputation: 222462

I would use a correlalated subquery, or a lateral join:

select u.*, c.*
from users u
cross apply (
    select string_agg(name) as all_computers
    from (
        select distinct c.name
        from computer c
        inner join logoninfo li on li.computer_id = c.computer_id
        where li.user_id = u.user_id and li.logoff_time = 0
    ) c
) c

Upvotes: 0

Jan Madeyski
Jan Madeyski

Reputation: 359

I did it like this. You can remove # sign, it means that I've used a temporary table.

SELECT u.name as [USER_NAME]
     , l.comps as [COMPUTERS]
FROM #user u
OUTER APPLY (
  SELECT STRING_AGG(c.name, ', ') as [comps]
  FROM (
    SELECT c.name
    FROM #logoninfo l
    JOIN #computer c ON l.computer_id = c.computer_id
    WHERE l.user_id = u.user_id
      AND l.logoff_time = 0
    GROUP BY c.name
  ) c
) l

And the result is:

enter image description here

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

Here is a way to do this.

Filter early and aggregate using string_agg

select userLogon.user_resource
         , string_agg(compRes.name, ' ,') as concat_computer
    from (
          select distinct 
                  user_id as "user_resource",
                  compRes.name as "comps"
            from logoninfo
            join computer compRes 
              on logoninfo.computer_id = compRes.computer_id
            join user us
              on logonginfo.user_id=us.user_id
            where logoninfo.logoff_time = 0
          ) userLogon
  group by userLogon.user_resource

Upvotes: 0

Related Questions