将表转换为0和1的矩阵

I have a table of Hosts and event Id's Like

我有一个主持人和事件ID的表

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

etc.

Now I want to convert them into a matrix like

现在我想将它们转换成像矩阵一样

             |  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 ....

How to do it in SQL?

如何在SQL中执行此操作?

2 个解决方案

#1

you have to use pivot to get this done, but this cannot be dynamic, you have to know the columns in your matrix before hand.

你必须使用pivot来完成这项工作,但这不是动态的,你必须事先知道矩阵中的列。

The below query works for event_id between 1 and 9, and if it is greater add it to the select and pivot clauses accordingly.

以下查询适用于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

you can learn more about pivot from here http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

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

Implementation of dynamic pivot for the above sql

为上面的sql实现动态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)

#2

While it is possible to construct pivots dynamically, typically it's far easier and more flexible to handle the display logic st the application level (e.g. with a simple PHP loop)

虽然可以动态构建枢轴,但通常可以更轻松,更灵活地处理应用程序级别的显示逻辑(例如,使用简单的PHP循环)

#1