SQL processing data with commas (,) and SQL comma (,)

SQL processing data with commas (,) and SQL comma (,)

The purpose of this blog is to use SQL to convert data with commas into rows in the table and splice the rows in the table into data consisting of commas.

Write the following code into comments:

-- Convert data with commas into a table Storage id and add data GODECLARE @ Text VARCHAR (200) SET @ Text = 'test1, test2 'Clare @ xml XMLSET @ xml = CONVERT (XML, '<a>' + REPLACE (@ Text, '</a> <a> ') + '</a>') create table # TMP1 (iID VARCHAR (50), sText VARCHAR (200) insert into # TMP1SELECT NEWID (), B. valueFROM (SELECT @ xml AS 'xml') implements ss apply (SELECT Tb. a. value ('. ', 'varchar (200) ') as value FROM. xml. nodes ('/A') AS Tb ()) bSELECT * FROM # TMP1GO -- Concatenates the data of a column in a Table into a comma separated form go create table # TMP2 (iID INT, sText VARCHAR (200 )) insert into # TMP2 (iID, sText) VALUES (1, 'test1'); insert into # TMP2 (iID, sText) VALUES (1, 'test2 '); DECLARE @ TMP2RES VARCHAR (200) SET @ TMP2RES = (SELECT sText + ', 'From # TMP2 for xml path ('') SET @ TMP2RES = SUBSTRING (@ TMP2RES, 0, LEN (@ TMP2RES)-1) SELECT @ TMP2RESGO

Running result