根据列中的值将单行转换为多行 [英] Converting single row into multiple rows based on values in columns

问题描述

我有一个表格，其中每条记录代表一个人，并且有许多列用于表明他们参加了哪些活动:

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'

例如:

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 |
\---------------|---------/

实际上我有超过 3 个事件，但以上是为了便于解释(顺便说一下，这不是作业问题).由于事件将来可能会发生变化，而且我无法控制传递的数据，因此我确实需要一个可以处理任意数量的可能事件列的动态解决方案.

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.

我假设我可以用 UNPIVOT 做一些事情，但我就是想不通，或者在 SO 或其他地方找到一个很好的例子 - 有人可以帮忙吗?

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?

推荐答案

想出了我想到的解决方案，但是是的，它确实需要动态 SQL 来获取相关列名以输入 UNPIVOT:

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!

这篇关于根据列中的值将单行转换为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！