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

MrFlinstone 501 Reputation points


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.

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points

    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'),  
    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,

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Naomi 7,361 Reputation points

    ;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 <> ''

    0 comments No comments