Matrix display from a SQL Server 2008 table

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.

The table looks like this.

enter image description here

I would like the matrix to look like this or similar.

enter image description here

similar question to pivot table: http://stackoverflow.com/questions/1069677/pivot-table-in-c — Massimo Zerbini, Oct 12 '11 at 07:32

dan_l · Answer 1 · 2011-10-12T08:09:18.837

1

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

edited Oct 12 '11 at 08:09

answered Oct 12 '11 at 07:58

dan_l

1,692
2
23
40

edited to handle any number of servers and apps. You can put it into store procedure for easier integration with your asp.net code – dan_l Oct 12 '11 at 08:03
in your asp.net code , you can just call the stored procedure GetPivotTable with ado.net to get the pivot table . – dan_l Oct 12 '11 at 08:10
Thanks for the replies. Both look interesting. I'll have to look into pivot tables a bit more as I've not used them before. – RickBowden Oct 12 '11 at 09:08
I've used your example of a TSQL query to create a pivot table and it it working great, but at the moment I have 1's and 0's in the grid where I would really like to replace them with images (as in the picture I posted). My gridview is set to auto generate the columns because I've no idea what the column names will be for each query. Do you know how I can change the 1's and 0's to images? – RickBowden Oct 24 '11 at 07:27

Jamiec · Answer 2 · 2011-10-12T07:46:03.853

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

Matrix display from a SQL Server 2008 table

2 Answers2