CODEWITHSUNDEEP
sqlsql-server
whatscool
whatscool

Reputation: 317

Using STUFF in SQL with a condition

I have three tables: Projects, ProjectAccess and Users:

+-----------+----------------+
| IdProject |  Project Name  |
+-----------+----------------+
|         1 | First Project  |
|         2 | Second Project |
|         3 | Third Project  |
+-----------+----------------+

+-----------+---------+
| IdProject | IdUsers |
+-----------+---------+
|         1 |       2 |
|         1 |       1 |
|         2 |       2 |
|         2 |       3 |
|         2 |       4 |
+-----------+---------+

+---------+----------+
| IdUsers | Username |
+---------+----------+
|       1 | Tom      |
|       2 | Dick     |
|       3 | Harry    |
|       4 | Bob      |
+---------+----------+

I want to write a query that returns a row from Projects that includes a field with all the comma separated usernames of user accounts that have access to that project (as defined in ProjectAccess). i.e. Results look like this:

+-----------+----------------+------------------+
| IdProject |  ProjectName   | Users w/ Access  |
+-----------+----------------+------------------+
|         1 | First Project  | Tom, Dick        |
|         2 | Second Project | Dick, Harry, Bob |
|         3 | Third Project  |                  |
+-----------+----------------+------------------+

I understand I have to join the tables together and use STUFF in the select, I'm not sure how exactly to accomplish this.

Anyone point me in the right direction?

Thanks

Upvotes: 0

Views: 2912

Answers (4)

IShubh
IShubh

Reputation: 364

Try the following query-:

select IdProject,[Project Name],[Users w/ Access]= COALESCE(STUFF(( 
select Distinct ','+ Username 
from Users a 
join ProjectAccess b 
on a.IdUsers =b.IdUsers 
and b.IdProject =p.IdProject  
FOR XML PATH ('')), 1, 1, ''
),'') from Projects p

SQL Server

Upvotes: 0

Barry Piccinni
Barry Piccinni

Reputation: 1791

You can achieve this fairly simply using the STRING_AGG function as such:

SELECT p.Id, p.projectName , STRING_AGG(u.Name, ', ') Names
FROM project p
JOIN projectAccess AS pa ON p.Id = pa.Id
JOIN users AS u on pa.userId = u.userId
GROUP BY p.Id, p.projectName

Here is a demo of this in action: SQLFiddle

EDIT: You can change both joins to a left join if you want to see the empty third project. Demo of this: SQLFiddle

Upvotes: 0

EzLo
EzLo

Reputation: 14189

You can use STUFF with FOR XML as correlated subquery.

SELECT
    IdProject = P.IdProject,
    [Project Name] = P.[Project Name],
    [Users w/ Access] = STUFF (
        (
            SELECT
                ', ' + U.Username
            FROM
                ProjectAccess AS A
                INNER JOIN Users AS U ON A.IdUsers = U.IdUsers
            WHERE
                A.IdProject = P.IdProject -- Link the correlated query with the outmost Project (P) table
            FOR XML
                PATH ('')
        ),
        1, -- Replace a string that starts at position number 1 (first character)
        2, -- for the next 2 characters (the leading ', ')
        '') -- with an empty string
FROM
    Projects AS P

This basically lists all projects but for each one, retrieves a comma separated list of each related username. The STUFF function replaces the leading comma while the FOR XML clause makes the subquery actually return 1 row with 1 value instead of a full table.

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

Try this query:

select idprojects,projectsname,STRING_AGG(username, ',') from
(    
select a.idprojects, a.projectname,c.username
    from  
    Projects a inner join ProjectAccess b 
    on a.idprojects=b.idprojects inner join Users c
    on b.idusers=c.idusers)x
group by idprojects,projectsname

Upvotes: 1

Related Questions