-- table definition
CREATE TABLE [dbo].[server]
(
[server_id] [int] IDENTITY(1,1) NOT NULL,
[server_name] [varchar](30) NULL,
[product] [varchar](30) NULL,
[data_centre] [varchar](100) NULL,
[business_unit] [varchar](100) NULL,
CONSTRAINT
[pk_server] PRIMARY KEY NONCLUSTERED ( [server_id] ASC)
)
-- use pivot to display the number of servers by product for each business unit and data centre.
DECLARE
@products varchar(max)
,@sql varchar(max)
SELECT @products = STUFF(( SELECT DISTINCT
'],[' + product
FROM
dbo.[server]
ORDER BY
'],[' + product
FOR XML PATH('')
), 1, 2, '') + ']'
SET @sql = '
SELECT
*
FROM
(
SELECT product, business_unit, data_centre
FROM dbo.[server]
) t
PIVOT (count(product) FOR product
IN(' + @products + ')) as pvt
'
EXEC (@sql)
Sample Results
| business_unit | data_centre | IMAGE | MySQL | NAS | SNAP | SOLR | SQL SERVER |
|---|---|---|---|---|---|---|---|
| UnitA | Datacentre1 | 0 | 10 | 0 | 0 | 6 | 5 |
| UnitB | Datacentre1 | 1 | 3 | 1 | 0 | 0 | 9 |
| UnitA | Datacentre2 | 0 | 7 | 0 | 1 | 3 | 6 |
| UnitB | Datacentre2 | 1 | 1 | 0 | 0 | 1 | 2 |
| UnitA | Datacentre3 | 0 | 2 | 0 | 0 | 0 | 2 |
| UnitB | Datacentre3 | 0 | 1 | 0 | 0 | 0 | 4 |
