Merge column values as comma separated string year wise in SQL Server

Last Reply on Sep 25, 2017 01:27 AM By kalpesh

Posted on Sep 24, 2017 11:08 PM

Hi,

 

I have a employee table, i want to get the year wise employees depend on Joining Resiging Dates and Status

 

CREATE TABLE [dbo].[Employee](
	[Emp_No] [numeric](18, 0) NULL,
	[Emp_Number] [nvarchar](50) NULL,
	[Emp_Name] [nvarchar](50) NULL,
	[Emp_JoiningDate] [date] NULL,
	[Emp_ResignDate] [date] NULL,
	[Emp_Status] [nvarchar](50) NULL
) ON [PRIMARY]

GO

--BELOW IS DATA

INSERT INTO [Surajit_Test].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status])
     VALUES(
           1
           ,'A-001'
           ,'Alex'
           ,'2013-01-01'
           ,'2013-08-24'
           ,'Resigned')
GO

INSERT INTO [Surajit_Test].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status])
     VALUES(
           2
           ,'A-002'
           ,'Adam'
           ,'2013-01-01'
           ,null
           ,'On Board')
GO

INSERT INTO [Surajit_Test].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status])
     VALUES(
           3
           ,'A-003'
           ,'Maxwell'
           ,'2014-01-01'
           ,null
           ,'On Board')
GO

INSERT INTO [Surajit_Test].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status])
     VALUES(
           4
           ,'A-004'
           ,'Smith'
           ,'2014-01-01'
           ,'2014-08-20'
           ,'Resigned')
GO


The O/P is

2013- A-001,A-002

2014-A-002,A-003,A-004

2015-A-002,A-003

2016-A-002,A-003

2017-A-002,A-003

2018-A-002,A-003

Employee A-002 & A-003 will show Until they Resigned.

 

Thanks

Basit.

 

You are viewing reply posted by: kalpesh on Sep 25, 2017 01:27 AM.
Posted on Sep 25, 2017 01:27 AM Modified on on Sep 25, 2017 01:52 AM

Hi basit0079,

As per your required output result you need to check year values of column Emp_JoiningDate also Emp_ResignDate.

First you need to find the minimum value of Emp_JoiningDate value and then you have to loop until current year pulse one value.

In Loop you need to match Temporary stored year value with Employee table Emp_JoiningDate and Emp_ResignDate column values also need to use COALESCE to get all employeecode as string output which will satisfy the condition.

Please check How to use COALESCE function by referring below Article link.

Select Column values as Comma Separated (Delimited) string in SQL Server using COALESCE

Also refer the below Test Query For your Question Answer Result.

SQL

-- To Declare year value 
-- Set minimum year valur from Employee joining date
DECLARE @Year INT 
SET @Year = (SELECT MIN(DATEPART(YEAR,Emp_JoiningDate)) FROM Employee)

-- Declare the Temporary Table
CREATE Table #TempEmployee ([Year] INT , EmployeeCode VARCHAR(max))

-- Loop until Year value is less than or equalt to current year pluse one year value
WHILE @Year <=  DATEPART(Year,GETDATE()) + 1 
BEGIN
	-- Set variable which will hold the EmployeeCode Numbers 

	DECLARE @EmpNumber VARCHAR(MAX) 
	SET @EmpNumber = NULL
	SELECT @EmpNumber = COALESCE(@EmpNumber + ',', '') + CAST(Emp_Number AS VARCHAR(10))
	FROM Employee
	WHERE DATEPART(YEAR,Emp_JoiningDate) <=  @Year AND ISNULL(DATEPART(YEAR,Emp_ResignDate),9999) >= @Year

	-- Insert into TempTable
	INSERT INTO #TempEmployee 
	SELECT @Year,@EmpNumber

	-- Increament Year value
	SET @Year = @Year + 1
END
GO

SELECT * FROm #TempEmployee
DROP TABLE #TempEmployee

OutPut

Year EmployeeCode
2013 A-001,A-002
2014 A-002,A-003,A-004
2015 A-002,A-003
2016 A-002,A-003
2017 A-002,A-003
2018 A-002,A-003

OR

-- To Declare year value 
-- Set minimum year valur from Employee joining date
DECLARE @Year INT 
SET @Year = (SELECT MIN(DATEPART(YEAR,Emp_JoiningDate)) FROM Employee)

-- Declare the Temporary Table
CREATE Table #TempEmployee ([YearWiseEmployeeDetails] VARCHAR(max))

-- Loop until Year value is less than or equalt to current year pluse one year value
WHILE @Year <=  DATEPART(Year,GETDATE()) + 1 
BEGIN
	-- Set variable which will hold the EmployeeCode Numbers 

	DECLARE @EmpNumber VARCHAR(MAX) 
	SET @EmpNumber = NULL
	SELECT @EmpNumber = COALESCE(@EmpNumber + ',', '') + CAST(Emp_Number AS VARCHAR(10))
	FROM Employee
	WHERE DATEPART(YEAR,Emp_JoiningDate) <=  @Year AND ISNULL(DATEPART(YEAR,Emp_ResignDate),9999) >= @Year

	-- Insert into TempTable
	INSERT INTO #TempEmployee 
	SELECT CAST(@Year AS VARCHAR(4))+'-'+@EmpNumber

	-- Increament Year value
	SET @Year = @Year + 1
END
GO

SELECT * FROm #TempEmployee
DROP TABLE #TempEmployee

OutPut

YearWiseEmployeeDetails
2013-A-001,A-002
2014-A-002,A-003,A-004
2015-A-002,A-003
2016-A-002,A-003
2017-A-002,A-003
2018-A-002,A-003