問題描述
我有一個(gè)表格，其中每條記錄代表一個(gè)人，并且有許多列用于表明他們參加了哪些活動(dòng):
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 |
\---------------|---------|---------|---------/
我想生成一個(gè)誰參加了哪些活動(dòng)的列表，所以我想要的輸出是:
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 |
\---------------|---------/
實(shí)際上我有超過 3 個(gè)事件，但以上是為了便于解釋(順便說一下，這不是作業(yè)問題).由于事件將來可能會(huì)發(fā)生變化，而且我無法控制傳遞的數(shù)據(jù)，因此我確實(shí)需要一個(gè)可以處理任意數(shù)量的可能事件列的動(dòng)態(tài)解決方案.
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.
我假設(shè)我可以用 UNPIVOT 做一些事情，但我就是想不通，或者在 SO 或其他地方找到一個(gè)很好的例子 - 有人可以幫忙嗎?
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?
推薦答案
想出了我想到的解決方案，但是是的，它確實(shí)需要?jiǎng)討B(tài) SQL 來獲取相關(guān)列名以輸入 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)
在這個(gè)例子中，我假設(shè)事件列從表中的第二列開始，但顯然我可以在子查詢中使用一些不同的邏輯來識(shí)別相關(guān)列.
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.
在我的示例數(shù)據(jù)上，這給出了所需的結(jié)果:
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 |
\---------------------------/
我想我更喜歡這個(gè)而不是使用游標(biāo)，盡管我實(shí)際上還沒有確認(rèn)這兩種動(dòng)態(tài)方法之間有什么性能差異(如果有的話).
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.
感謝大家對這個(gè)問題的幫助和建議，一如既往地非常感謝！
Thanks for everyone's help and suggestions on this question though, greatly appreciated as always!
這篇關(guān)于根據(jù)列中的值將單行轉(zhuǎn)換為多行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！