Here's my sql query
SLEECT Employee, BeginDate, Status
FROM HRHISTORY
WHERE EMPLOYEE 1868
Result
Employee BeginDate Status
1868 2017--7-03 Full Time
1868 2015 - 09-14 Part Time
1868 2015-06-05 Retire
1868 1995-03-28 Full Time
What I want is
Employee BeginDate1 Status BeginDate2 Status 2 BeginDate3 Status 3 BeginDate4 Status4
1868 1995-03-28 FT 2015-06-05 Ret 2015-09-14 PT 2017 -now FT
Thank you in advance.
I tried with this query and didn't work
SELECT EMPLOYEE.EMPLOYEE,
EMPLOYEE.LAST_NAME,
EMPLOYEE.FIRST_NAME,
'HIRE_DATE1' AS HIRE_DATE1,
'HIRE_DATE2' AS HIRE_DATE2,
'HIRE_DATE3' AS HIRE_DATE3,
'HIRE_DATE4' AS HIRE_DATE4,
HRHISTORY.BEG_DATE AS HISTORY_DATE,
EMPLOYEE.EMP_STATUS AS CURRENT_STATUS,
HRHISTORY.A_VALUE AS HISTORY_STATUS,
'STATUS1' AS EMP_STATUS1,
'STATUS1' AS EMP_STATUS1,
'STATUS1' AS EMP_STATUS1,
'STATUS1' AS EMP_STATUS1
FROM
(SELECT EMPLOYEE.EMPLOYEE,
EMPLOYEE.LAST_NAME,
EMPLOYEE.FIRST_NAME,
HRHISTORY.BEG_DATE AS HISTORY_DATE,
EMPLOYEE.EMP_STATUS AS CURRENT_STATUS,
HRHISTORY.A_VALUE AS HISTORY_STATUS
FROM HRHISTORY
INNER JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEE = HRHISTORY.EMPLOYEE
WHERE HRHISTORY.EMPLOYEE = 1868 AND FLD_NBR = 20
AND A_VALUE <> 'T1'
) X
PIVOT
(
COUNT(EMPLOYEE) FOR X.HISTORY_DATE IN ([HIRE_DATE1], [HIRE_DATE2], [HIRE_DATE3], [HIRE_DATE4])
) AS P;
PLEASE ADVICE
We need more information - what didn't work? Did you get an error - invalid results - something else?
hi
i tried to do this ..
i have gotten till here .
i have to get the column names .. and statuses also
i will work on this tomorrow
.. unless experts on this forum decide to help you out and do it very very quickly
please click arrow to the left for Drop Create Sample data
drop table #temp
create table #temp
(
rn int identity(1,1) ,Employee int , BeginDate date,Status varchar(10)
)
go
insert into #temp select 1868,'2017-07-03','Full Time'
insert into #temp select 1868,'2015-09-14','Part Time'
insert into #temp select 1868,'2015-06-05','Retire'
insert into #temp select 1868,'1995-03-28','Full Time'
select * from #temp
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N',' + QUOTENAME(rn) FROM ( select rn from #temp ) AS x;
SELECT @columns = right(@columns,len(@columns)-1)
SET @sql = N'
select Employee , '+replace(replace(@columns,'[','max(['),']','])')+' from (
SELECT Employee,' + @columns+'
FROM
(
select rn ,Employee , BeginDate,Status from #temp
) AS j
PIVOT
(
min(BeginDate) FOR rn IN (' + @columns + ')
) p) a group by Employee;';
PRINT @sql;
EXEC sp_executesql @sql;
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);
1 Like
