How to display record in matrix format in SQL Server

Question

How to display record in matrix format. I don't know how to display record in SQL Server query?

My Table

    Id   No      Month   Department      File
    1   IT.1    JAN       DEPT-1     JOINING REPORT-1.doc
    2   IT.1    JAN       DEPT-1     JOINING REPORT-1.doc
    3   IT.1    JAN       DEPT-2     JOINING REPORT-1.doc
    4   IT.2    FEB       DEPT-1     JOINING REPORT-1.doc
    5   IT.3    MAR       DEPT-3     JOINING REPORT-1.doc
    6   IT.3    MAR       DEPT-2     JOINING REPORT-1.doc

Expected output

         No      JAN FEB MAR  APR
        IT.1      2   0   0    0
        IT.2      0   1   0    0
        IT.3      0   0   2    0
        IT.4      0   0   0    0
        IT.5      0   0   0    0

Please don't tag `oracle`, `mysql` for `sqlserver` question. — Lalit Kumar B, Apr 09 '18 at 06:36

score 0 · Answer 1 · answered Apr 09 '18 at 06:39

Try this:

SELECT [No]
    ,ISNULL(COUNT(DISTINCT CASE WHEN Month='JAN' THEN Department END),0) JAN
    ,ISNULL(COUNT(DISTINCT CASE WHEN Month='FEB' THEN Department END),0) FEB
    ,ISNULL(COUNT(DISTINCT CASE WHEN Month='MAR' THEN Department END),0) MAR
    ,ISNULL(COUNT(DISTINCT CASE WHEN Month='APR' THEN Department END),0) APR
FROM Your_Table
GROUP BY [No]

score 0 · Answer 2 · answered Apr 09 '18 at 06:43

We can use PIVOT to transpose rows to columns.

declare @xyz table (Id int, [No] varchar(100), [Month] varchar(100), Department varchar(100), [File] varchar(100))

insert into @xyz
select     1,   'IT.1',    'JAN',       'DEPT-1',     'JOINING REPORT-1.doc' union all
select     2,   'IT.1',    'JAN',       'DEPT-1',     'JOINING REPORT-1.doc' union all
select     3,   'IT.1',    'JAN',       'DEPT-2',     'JOINING REPORT-1.doc' union all
select     4,   'IT.2',    'FEB',       'DEPT-1',     'JOINING REPORT-1.doc' union all
select     5,   'IT.3',    'MAR',       'DEPT-3',     'JOINING REPORT-1.doc' union all
select     6,   'IT.3',    'MAR',       'DEPT-2',     'JOINING REPORT-1.doc'

select
    *
from
    (
        select [No], [Month] from @xyz
    ) AS SRC
    PIVOT
    (
        COUNT([Month]) FOR [Month] in ([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])
    ) AS PVT

How to display record in matrix format in SQL Server

2 Answers2