转换单列成
我有每个记录代表一个人的桌子以及用于指示他们参加什么活动的列列基于价值观多行：转换单列成
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 |
\---------------|---------|---------|---------/
我要生成谁出席了活动，所以我期望的输出是一个列表：
/---------------|---------|
| 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个事件，但以上是为了便于解释（这是而不是作业问题btw）。由于事件将来可能会发生变化，而且我无法控制传递的数据，所以我真的需要一个动态解决方案，它可以处理任意数量的可能事件列。
我假设我可以用UNPIVOT做一些事情，但我无法弄清楚，或找到一个很好的例子，在SO或其他地方工作 - 有人可以帮忙吗？
试着这么做
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
动态SQL你可以尝试这样的事：
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);
我做到这一点使用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'
你可以用逆透视做到这一点正如你所说的，你只需要确保你告诉它是什么事件，否则你只会得到一个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
与其他答案一样，这需要我每次更改Event列（添加新事件，重命名事件或移除事件）时重新编写查询，所以这不会对我有所帮助。 – 3N1GM4
了解，这不是你原来的问题，因此上面的代码。会有一个想法。 – Leonidas199x
想通了，我想的解决方案，但肯定的，它确实需要动态SQL来获得相关列名送入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)
在这个例子中，我提出假设Event列是从表中的第二列开始的，但显然我可以在子查询中使用一些不同的逻辑来确定相关列（如有必要）。
在我的示例数据，这给期望的结果：
/---------------------------\
| 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 |
\---------------------------/
我想我更喜欢这对使用游标，虽然我还没有真正证实了性能上的差异（如果有的话）存在之间两种动态方法。
感谢大家对这个问题的帮助和建议，尽管如此，非常感谢！
但是如果我有200个事件列，我需要200个子查询'UNION'ed在一起？我正在寻找一种方法，如果可能的话，动态地做到这一点。 – 3N1GM4
您是否为每个新事件生成新列？ – Yeou
我不是个人，但我正在传递的数据集是这样做的，是的。 – 3N1GM4