[Solved] How to Select Column values as Comma Separated ?  

  RSS

TopicCreator
(@topiccreator)
Eminent Member Moderator
Joined: 1 year ago
Posts: 12
06/06/2018 1:37 pm  

Hi I want to get  "Column values as Comma Separated" and then I have to use them in SQL IN(Column Values). Means I have to Split them by comma. 


ReplyQuote
TechGroup Admin
(@admin)
Eminent Member Admin
Joined: 1 year ago
Posts: 11
06/06/2018 1:47 pm  

1. Use this query for select Employees Comma Separated.

(SELECT STUFF((SELECT ',' + Cast(EmployeeID as varchar(10))FROM tblEmployee where
CompanyID = 1 FOR XML PATH('')), 1, 1, '') as EmployeeIDs)

2. Then Create a function for split them by Comma.

CREATE FUNCTION [dbo].[SplitString]

(

@Input NVARCHAR(MAX),

@Character CHAR(1)

)

RETURNS @Output TABLE (

Item NVARCHAR(1000)

)

AS

BEGIN

DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1

IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

BEGIN

SET @Input = @Input + @Character

END

WHILE CHARINDEX(@Character, @Input) > 0

BEGIN

SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)

SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

END

RETURN

END

3. Then Use this function with Split function.

Select * From tblManager where EmployeeID  in
(SELECT item FROM dbo.Splitstring((SELECT STUFF((SELECT ',' + Cast(EmployeeID as varchar(10))FROM tblEmployee where CompanyID = 1 FOR XML PATH('')), 1, 1, '') as EmployeeIDs), ','))


ReplyQuote
Share:
  
Working

Please Login or Register