Question

Does anyone know how to create a matrix display from a database table?
I m using ASP.NET C# and the database is SQL Server 2008.

表格就这样看。

enter image description here

我希望矩阵像这样。

“entergraph

Answer 1

使用TSQL pivot

create table table1
(
    serverName varchar(30),
    app varchar(50)
);
go

insert table1 (serverName , app) values ( server1  ,  app A );
insert table1 (serverName , app) values ( server2  ,  app A );
insert table1 (serverName , app) values ( server2  ,  app B );
insert table1 (serverName , app) values ( server3  ,  app B );
insert table1 (serverName , app) values ( server1  ,  app C );
insert table1 (serverName , app) values ( server3  ,  app C );
go

create procedure GetPivotTable
as begin
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(@PivotColumnHeaders +  ,[  + cast(t.serverName as varchar) +  ]  ,
   [  + cast(t.serverName as varchar)+  ] )
FROM (select distinct serverName from table1) t


DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N 
select * from
(select  app, serverName from table1)  sourceTable
pivot
(
    count(serverName) for serverName in (  + @PivotColumnHeaders +  )
) pivottable
 
EXECUTE(@PivotTableSQL)
end

go

exec GetPivotTable

Answer 2

My suggestion here would be to pull the data out in exactly the format you have it, into a List of entities (at its simplest):

public class ServerApplicationRelationship
{
   public string Server{get;set;}
   public string Application{get;set;}
}

并形成一种习惯控制,使一个使用<代码>的超文本表格成为可能。 List<ServerApplicationRelationship> as its datasource.

从所提供的数据来看,表应十分容易。

如果你really想要从 s中提取数据,你可以使用 que等方法。

select application,
          case when exists(select 1 from example where application=ex.application and server= server 1 ) THEN 1 ELSE 0 end as [server 1],
          case when exists(select 1 from example where application=ex.application and server= server 2 ) THEN 1 ELSE 0 end as [server 2],
          case when exists(select 1 from example where application=ex.application and server= server 3 ) THEN 1 ELSE 0 end as [server 3]
    from yourTable ex
    group by application

友情链接