Converting single row into multiple rows based on values in columns

Question

I have a table where each record represents a person and there are many columns used to indicate what events they attended:

CREATE TABLE EventAttendees
(
    Person VARCHAR(100),
    [Event A] VARCHAR(1),
    [Event B] VARCHAR(1),
    [Event C] VARCHAR(1)
)

INSERT INTO EventAttendees
SELECT 'John Smith','x',NULL,NULL
UNION
SELECT 'Jane Doe',NULL,'x','x'
UNION
SELECT 'Phil White','x',NULL,'x'
UNION
SELECT 'Sarah Jenkins','x','x','x'

Which looks like this for example:

SELECT * FROM Event Attendees

/---------------|---------|---------|---------\
| Person        | Event A | Event B | Event C |
|---------------|---------|---------|---------|
| John Smith    |    x    |   NULL  |   NULL  |
| Jane Doe      |   NULL  |    x    |    x    |
| Phil White    |    x    |   NULL  |    x    |
| Sarah Jenkins |    x    |    x    |    x    |
\---------------|---------|---------|---------/

I want to generate a list of who attended which events, so my desired output is:

/---------------|---------|
| Person        | Event   |
|---------------|---------|
| John Smith    | Event A |
| Jane Doe      | Event B |
| Jane Doe      | Event C |
| Phil White    | Event A |
| Phil White    | Event C |
| Sarah Jenkins | Event A |
| Sarah Jenkins | Event B |
| Sarah Jenkins | Event C |
\---------------|---------/

In reality I have many more than 3 events, but the above is for ease of explanation (This is not a homework question btw). As the Events might change in the future and I have no control over the data I am being passed, I really need a dynamic solution which can handle any number of possible event columns.

I'm assuming I can do something with UNPIVOT, but I just can't figure it out, or find a good example on SO or elsewhere to work from - can someone help?

Gordon Linoff · Accepted Answer · 2017-09-14 11:17:06Z

3

I do this using outer apply:

select ea.person, v.EventName
from EventAttendees ea outer apply
     (values ('Event A', [Event A]),
             ('Event B', [Event B]),
             ('Event C', [Event C])
     ) v(EventName, EventFlag)
where v.EventFlag = 'x'

answered Sep 14, 2017 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

3N1GM4 Over a year ago

As per Yeou's answer, this would require a separate line within values for each Event - I have hundreds of events, so would rather not have to put together that large a statement if possible. Also, the Events might change in the future, so it really needs to be dynamic. I will update the question to be more clear.

Gordon Linoff Over a year ago

@3N1GM4 . . . Your sample data has three events columns. You have a problem with your application if you are routinely adding columns to a table.

3N1GM4 Over a year ago

I agree @GordonLinoff, but unfortunately that is outside of my control.

Keith · Accepted Answer · 2017-09-14 11:20:01Z

2

You can do it with unpivot as you said, you would just need to ensure you are telling it what event it is for, otherwise you just get an X:

CREATE TABLE #tmpEventAttendees
(
    Person VARCHAR(100),
    [Event A] VARCHAR(1),
    [Event B] VARCHAR(1),
    [Event C] VARCHAR(1)
)
INSERT INTO #tmpEventAttendees
SELECT 'John Smith','x',NULL,NULL
UNION
SELECT 'Jane Doe',NULL,'x','x'
UNION
SELECT 'Phil White','x',NULL,'x'
UNION
SELECT 'Sarah Jenkins','x','x','x'

SELECT Person, [Event]
FROM
(
  SELECT    Person                                                                                  , 
            CASE WHEN [Event A] IS NOT NULL THEN 'Event A' END AS [Event A]                             , 
            CASE WHEN [Event B] IS NOT NULL THEN 'Event B' END AS [Event B]                             ,
            CASE WHEN [Event C] IS NOT NULL THEN 'Event C' END AS [Event C]
  FROM #tmpEventAttendees
) AS cp
UNPIVOT 
(
  [Event] FOR [Events] IN ([Event A], [Event B], [Event C])
) AS up;

DROP TABLE #tmpEventAttendees

answered Sep 14, 2017 at 11:20

Keith

1,03611 silver badges22 bronze badges

2 Comments

3N1GM4 Over a year ago

As with the other answers, this requires me to re-write the query every time the Event columns change (adding new events, renaming events or removing events), so this is not going to help me.

Keith Over a year ago

Understood, that wasn't in your original question hence the code above. Will have a think.

Yeou · Accepted Answer · 2017-09-14 11:43:13Z

2

Try something like

SELECT * FROM (
    SELECT Person, CASE WHEN [Event A] = 'x' THEN 'Event A' END AS [Event] FROM EventAttendees
    UNION 
    SELECT Person, CASE WHEN [Event B] = 'x' THEN 'Event B' END AS [Event] FROM EventAttendees
    UNION 
    SELECT Person, CASE WHEN [Event C] = 'x' THEN 'Event C' END AS [Event] FROM EventAttendees
    ) AS EventAttendees
    WHERE Event is not null
    order by Person

For dynamic sql you can try something like this:

    DECLARE @name varchar(30)
    DECLARE @sql varchar(1000) = 'SELECT * FROM (';
    DECLARE NameCursor CURSOR
        FOR select name from sys.all_columns where object_id = (select object_id from sys.tables where name='EventAttendees') and name!='Person'
    OPEN NameCursor
    FETCH NEXT FROM NameCursor INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @sql += 'SELECT Person, CASE WHEN [' + @name+'] = ''x'' THEN ''' + @name +''' END AS [Event] FROM EventAttendees'   
        FETCH NEXT FROM NameCursor INTO @name

        IF(@@FETCH_STATUS = 0)
        BEGIN
            SET @sql += ' UNION ';
        END

    END;
    CLOSE NameCursor;  
    DEALLOCATE NameCursor;
    SET @sql += ') AS EventAttendees
            WHERE Event is not null
            order by Person';

    execute (@sql);

edited Sep 14, 2017 at 11:43

answered Sep 14, 2017 at 11:17

Yeou

6321 gold badge7 silver badges21 bronze badges

7 Comments

3N1GM4 Over a year ago

But if I have 200 Event columns, I need 200 subqueries UNIONed together? I'm looking for a way to do this dynamically if possible.

Yeou Over a year ago

are you generating new column for every new event?

3N1GM4 Over a year ago

I'm not personally, but the dataset I'm being passed does it that way, yes.

Yeou Over a year ago

In that case you'll need to create dynamic sql

3N1GM4 Over a year ago

Really? There's no way to do this without dynamic SQL? I was sure I'd seen something similar before, but I can't find it now.

|

3N1GM4 · Accepted Answer · 2017-10-09 11:27:19Z

0

Figured out the solution I was thinking of, but yes, it does require dynamic SQL to get the relevant column names to feed into the UNPIVOT:

declare @sql varchar(max)
set @sql = 
    'select Person, EventName
    from EventAttendees
    unpivot
    (
        Attended for EventName in (' + (select
                                        stuff((
                                            select ',' + QUOTENAME(c.[name])
                                            from sys.columns c
                                            join sys.objects o on c.object_id = o.object_id
                                            where o.[name] = 'EventAttendees'
                                            and c.column_id > 1
                                            order by c.[name]
                                            for xml path('')
                                        ),1,1,'') as colList) + ')
    ) unpiv
    where unpiv.Attended = ''x''
    order by Person, EventName'

exec (@sql)

In this example, I am making the assumption that the Event columns are from the second column in the table onwards, but obviously I could use some different logic within the subquery to identify the relevant columns if necessary.

On my example data, this gives the desired result:

/---------------------------\
| Person        | EventName |
|---------------|-----------|
| Jane Doe      | Event B   |
| Jane Doe      | Event C   |
| John Smith    | Event A   |
| Phil White    | Event A   |
| Phil White    | Event C   |
| Sarah Jenkins | Event A   |
| Sarah Jenkins | Event B   |
| Sarah Jenkins | Event C   |
\---------------------------/

I think I prefer this to using a cursor, although I haven't actually confirmed what performance difference (if any) there is between the two dynamic approaches.

Thanks for everyone's help and suggestions on this question though, greatly appreciated as always!

answered Oct 9, 2017 at 11:27

3N1GM4

3,3463 gold badges23 silver badges43 bronze badges

1 Comment

Tim Cooke Over a year ago

One potential downside to calling EXEC with a string (arbitrary SQL) is that the caller of the code needs permissions to execute the arbitrary query. If you create a STORED PROCEDURE called spGetAttendees and grant a service user permissions to EXEC spGetAttendees, it won't work unless the service user is also granted permissions to SELECT from EventAttendees. I agree that this is the best solution, given the need to work on an arbitrary number of event name columns. My point is the more general one that dynamic queries require more permissions than compiled (constant) SQL queries.

Collectives™ on Stack Overflow

Converting single row into multiple rows based on values in columns

4 Answers 4

3 Comments

2 Comments

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related