mysql - 将表转换为 0's and 1' s 的矩阵

Question

我有一张主机和事件 ID 的表

Hosts     |   Event_id
system1          1
System2          1
System1          2
System3          1
System2          2

等等

现在我想将它们转换成类似这样的矩阵

             |  1    2    3    4    5    6    7    8    9 ....
---------------------------------------------------------------------
    System1  |  1    1    0    1    1    0    1    0    0 ....
    System2  |  1    1    1    1    1    0    1    0    0 ....
    System3  |  1    0    0    1    1    0    1    0    0 ....

如何在 SQL 中实现？

Answer 1

您必须使用 pivot 来完成此操作，但这不能是动态的，您必须事先知道矩阵中的列。

下面的查询适用于 1 到 9 之间的 event_id，如果更大，则相应地将其添加到 select 和 pivot 子句中。

declare @t table 
(
 hosts VARCHAR(20), event_id int
)
insert into @t values ('system1','1')
insert into @t values ('System2','1')
insert into @t values ('System1','2')
insert into @t values ('System3','1')
insert into @t values ('System2','2')
insert into @t values ('System3','4')
select * from @t

Select Hosts,[1],[2],[3],[4],[5],[6],[7],[8],[9]
from 
(
select hosts,hosts as Hosts1,Event_id from @t 
) P
pivot 
(
count(Hosts1) for Event_id in ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) as pvt

您可以从这里了解更多关于 pivot 的信息 http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

上述sql的dynamic pivot的实现

CREATE TABLE #t
(
 hosts VARCHAR(20), event_id int
)
insert into #t values ('system1','1')
insert into #t values ('System2','1')
insert into #t values ('System1','2')
insert into #t values ('System3','1')
insert into #t values ('System2','2')
insert into #t values ('System3','4')
select * from #t

declare @sql varchar(4000)
declare @ColumnList VARCHAR(2000)

select @columnList = stuff((select ',[' + CAST(event_id AS VARCHAR) + ']' from (select distinct event_id from #t) a1  for xml path('')),1,1,'') -- get the concatenated list of the event_id columns seperated by a comma.
select @columnList

SET @sql = 
'Select Hosts,' + @columnList + '
from 
(
select hosts,hosts as Hosts1,Event_id from #t 
) P
pivot 
(
count(Hosts1) for Event_id in (' + @columnList + ')
) as pvt'
exec (@sql)