declare @t table (
name varchar(10),
smena varchar(10),
[1] varchar(1),
[2] varchar(1),
[3] varchar(1),
[4] varchar(1),
[5] varchar(1),
[6] varchar(1)
) 

insert into @t 
Values
('Иванов','день', '5','5','В','5','5','5'),
('Иванов','ночь', '2','2','В','2','2','2'),
('Петров','день', '5','В','5','5','5','5'),
('Петров','ночь', '2','В','2','2','2','2');


With UnP as (
Select name,Work,N_Day+smena as N_Day_smena From @t
 UNPIVOT (Work for N_Day in ([1],[2],[3],[4],[5],[6])) as u)
Select * 
From UnP
Pivot (max(Work) For N_Day_smena in ([1день],[1ночь],
                                     [2день],[2ночь],
									 [3день],[3ночь],
									 [4день],[4ночь],
									 [5день],[5ночь],
									 [6день],[6ночь])) p