-1

How to convert row data into comma separated values and back, in SQL Server?

James Z
  • 12,209
  • 10
  • 24
  • 44
Nishant Khanna
  • 59
  • 1
  • 1
  • 6
  • Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Dmitrij Kultasev Jul 10 '18 at 11:53

2 Answers2

1

You didnt provide proper details about your issue or your sample code. however assuming something with my experience you need to use STUFF option of sql server. below is my query which i used in my project. try this.. bye

SELECT STUFF((
                    SELECT ', ' + Tbl.[Column_Name]
                    FROM    [Table_Name] Tbl
                    WHERE   <Condition>
                    FOR XML PATH('')
                ),1,1,'')
Srinivasan
  • 293
  • 1
  • 6
  • 16
0

You need to create the following function: 'SplitString'. It is a table valued function and acceptes 2 parameters- @Input and @Character

CREATE FUNCTION 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
GO

How to call the function?

SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

Result

   Item
  ------
1  Apple
2  Banana
3  Guava
4  Mango

Or, if your SQL server version is above 2016, there is an in-built function called 

    STRING_SPLIT ( string , separator )
AswinRajaram
  • 1,519
  • 7
  • 18