Reports

On the t-sql pivot is little bit tricky. Please check whether this code work?


CREATE TABLE holiday  (
    Region VARCHAR(100),
    Date VARCHAR(100)
);

INSERT INTO holiday  (Region, Date)
VALUES 
('Scotland'         ,'2019-01-01'),
('Scotland'         ,'2019-01-03'),
('Scotland'         ,'2019-01-04'),
('England-and-Wales'    ,'2019-01-01'),
('England-and-Wales'    ,'2019-01-02'),
('England-and-Wales'    ,'2019-01-05'),
('Northern-Ireland' ,'2019-01-05')



SELECT 
    Date,[Scotland] AS Scotland,[England-and-Wales] AS [England-and-Wales],[Northern-Ireland] AS [Northern-Ireland]
FROM 
    (SELECT Date, Region, 1 AS Value FROM holiday ) AS SourceTable
PIVOT (MAX(Value) FOR Region IN ([Scotland], [England-and-Wales], [Northern-Ireland])
) AS PivotTable

Result:



Date        Scotland    England-and-Wales   Northern-Ireland
2019-01-01  1           1                   null
2019-01-02  null        1                   null
2019-01-03  1           null                null
2019-01-04  1           null                null
2019-01-05  null        1                   1

dbfiddle

79336442