this gets you half way?
use sqlteam
go
drop table korpus
drop table EmployeeSummation
create table korpus(Employee int, BeginDate date, Status varchar(20))
insert into korpus
select 1868 , '2017-07-03', 'Full Time' union all
select 1868 , '2015-09-14','Part Time' union all
select 1868 , '2015-06-05','Retire' union all
select 1868 , '1995-03-28','Full Time'
create table EmployeeSummation(
Employee int,
BeginDate date, Status varchar(20),
DateTag varchar(100),
StatusTag varchar(100)
)
insert into EmployeeSummation
select Employee,
BeginDate,
Status,
CONCAT('BeginDate', ROW_NUMBER() OVER(ORDER BY BeginDate ASC) ) DateTag,
CONCAT('Status', ROW_NUMBER() OVER(ORDER BY Status ASC)) StatusTag
from korpus
declare @columnDates varchar(max), @query varchar(max), @columnStatuses varchar(max)
SELECT @columnDates = COALESCE(@columnDates + ', ','') + QUOTENAME(DateTag)
FROM
(
select distinct DateTag
from EmployeeSummation
) AS B
ORDER BY B.DateTag
SELECT @columnStatuses = COALESCE(@columnStatuses + ', ','') + QUOTENAME(StatusTag)
FROM
(
select distinct StatusTag
from EmployeeSummation
) AS B
ORDER BY B.StatusTag
SET @query ='
select Employee, ' + CONCAT(@columnDates, ',', @columnStatuses) + ' from
(
Select Employee, BeginDate, DateTag, Status, StatusTag
From EmployeeSummation
) p
PIVOT
(
MAX(BeginDate)
FOR DateTag in (' + @columnDates + ')
) d
PIVOT
(
MAX(Status)
FOR StatusTag in (' + @columnStatuses + ')
) s
'
exec( @query);