SQLTeam.com | Weblogs | Forums

I want a single row for the employee information but mulitple columns for those emloyees with mulitple begin dates

Transact-SQL
#1

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.

#2

please see this

hope this helps

#3

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

#4

We need more information - what didn't work? Did you get an error - invalid results - something else?

#5

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;

image
image781×292 8.22 KB

#6

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);