How to achieve comma separated data from multiple rows in sql server?

Question

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

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

score 1 · Accepted Answer · answered Jul 10 '18 at 12:01

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,'')

answered Jul 10 '18 at 12:01

Srinivasan

293
1
6
16

can i achieve reverse of it. I mean i have comma separated string & want to convert it into row data? – Nishant Khanna Jul 10 '18 at 12:14

score 0 · Answer 2 · answered Jul 10 '18 at 12:12

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 )

How to achieve comma separated data from multiple rows in sql server?

2 Answers2