当前位置：文江博客话题详情

SQL Server 2008 表的矩阵显示

发布于 2024-12-09 03:45:19 字数 247 浏览 0 评论 0原文

有谁知道如何从数据库表创建矩阵显示？
我使用的是 ASP.NET C#，数据库是 SQL Server 2008。

该表如下所示。

在此处输入图像描述

我希望矩阵看起来像这样或类似的。

在此处输入图像描述

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

滿滿的愛 2024-12-16 03:45:19

使用 TSQL 枢轴

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

use 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

回复收藏 0 原文

骄傲 2024-12-16 03:45:19

我的建议是以您所拥有的格式将数据提取到实体列表中（最简单的）：

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

并创建一个自定义控件，该控件使用 List< /code> 作为其数据源。

从提供的数据中呈现该表应该很容易。

如果您确实想从 sql 中提取该形状的数据，您可以使用如下查询

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

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;}
}

And create yourself a custom control which renders an HTML table using the List<ServerApplicationRelationship> as its datasource.

It should be pretty easy to render that table from the data provided.

If you really want to pull the data out in that shape from sql, you could use a query such as

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

回复收藏 0 原文

~没有更多了~