問題描述
我有一個表格，其中每條記錄代表一個人，并且有許多列用于表明他們參加了哪些活動:
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!
這篇關于根據列中的值將單行轉換為多行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！