SQLServer - Converting Comma Separated Value to Rows and Vice Versa in SQL Server

Following script will create common separate values (CSV) or common separate list from tables. convert list to table. Following script is below.
CREATE TABLE CommaSeparated
   (  ID    int IDENTITY,
      TeamID      int,
      FirstName   varchar(50)
   )
 
-- Load Sample Data
INSERT INTO CommaSeparated VALUES ( 1, 'Maruthi' )
INSERT INTO CommaSeparated VALUES ( 1, 'Siva' )
INSERT INTO CommaSeparated VALUES ( 1, 'Prasad' )
INSERT INTO CommaSeparated VALUES ( 2, 'Parviz' )
INSERT INTO CommaSeparated VALUES ( 2, 'Javadian' )
INSERT INTO CommaSeparated VALUES ( 2, 'P' )
INSERT INTO CommaSeparated VALUES ( 3, 'Amit' )
INSERT INTO CommaSeparated VALUES ( 3, 'Kumar' )

Select * from CommaSeparated

--Retrieve data with semicolon
--Scenario1
SELECT
   t1.TeamID,
   MemberList = substring((SELECT ( '; ' + FirstName )
                           FROM CommaSeparated t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY
                              TeamID,
                              FirstName
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM CommaSeparated t1
GROUP BY TeamID


go
--Scenario2
select t1.TeamID,
memberlist=SUBSTRING((select ('; '+FirstName)
 from CommaSeparated t2 where t1.teamid=t2.TeamID
 order by TeamID,FirstName for xml path('')
 ),1,1000) from commaseparated t1
 group by t1.teamid

Happy Coding!!!!!!!!