How to write this query that will return an assignment for every user

Question

Hi

I have a data set similar to the example below.

what I would like to return is dataset where for every user, I would like to retrieve either group1 or group2 as long as its not null or blank. I was going to write a select query and union the results for the 2 columns, but I feel there must be a better way to do this. I want to supply a name and the groups to be returned (stored procedure or function). If the user only has 1 group assigned, then return one group, if the user has 2 groups, then return only the 2 unique groups and no duplicate.

Answer 1

Hi @MrFlinstone
According to your description, it seems that you want to perform a column-to-row operation.
Here I recommend two methods:
One is to use Cross Apply:

SELECT DISTINCT Name,Groups   
FROM your_dataset CROSS APPLY(VALUES(Group1,'Group1'),  
                                    (Group2,'Group2'))U(Groups,Group1or2)  
WHERE Groups IS NOT NULL AND Groups<>''  

The other is to use the UNPIVOT keyword,check this:

SELECT DISTINCT Name,Groups   
FROM your_dataset UNPIVOT(Groups FOR Group1or2 IN ([Group1],[Group2]))U  
WHERE Groups IS NOT NULL AND Groups <>''  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

;with cte as (select Name, City, c.Group
from Users
CROSS APPLY (values((Group1),(Group2)) c(Group))

select distinct Name, City, Group
from cte
where Group is not null and Group <> ''