How to convert multi-row string data of the same column into one row of data in the same column using SQL?

--- SELECT * FROM (SELECTDISTINCTcolumn1FROMtable1) Explain ssapply (SELECTcolumn2 (SELECT [*] column2FROMtable1WHEREcolumn1A. column1FORXMLPATH (), TYPE ). value (, nvarchar (20) B ---- SELECT * FROM (SELEC

--- First SELECT * FROM (select distinct column1 FROM table1) a cross apply (SELECT column2 = (SELECT [*] = column2 FROM table1 WHERE column1 =. column1 for xml path (''), TYPE ). value ('/', 'nvarchar (20) ') B ---- SELECT * FROM (SELEC

--- First

SELECT * FROM (select distinct column1 FROM table1) a cross apply (SELECT column2 = (SELECT [*] = column2 FROM table1 WHERE column1 =. column1 for xml path (''), TYPE ). value ('/', 'nvarchar (20) ') B ---- SELECT * FROM (SELECT DISTINCT column1 FROM table1)
Outer apply (SELECT [values] = STUFF (REPLACE (SELECT column2 FROM table1 N
WHERE column1 = A. column1 for xml auto ),' ', ''), 1, 1 ,'')
) Nselect * from table1select * from B --------------------------------------------------- CREATE FUNCTION dbo. f_str (@ id int)
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ r varchar (8000)
SET @ r =''
SELECT @ r = @ r + ',' + value
FROM tb
WHERE id = @ id
Return stuff (@ r, 1, 1 ,'')
END
GO
-- Call a function

SELECt id, values = dbo. f_str (id)
FROM tb
Group by id

-- 2. New Solution
-- Sample Data
DECLARE @ t TABLE (id int, value varchar (10 ))
INSERT @ t SELECT 1, 'A'
Union all select 1, 'bb'
Union all select 2, 'aaa'
Union all select 2, 'bbb'
Union all select 2, 'ccc'

-- Query Processing
SELECT *
FROM (
SELECT DISTINCT
Id
FROM @ t
)
Outer apply (
SELECT
[Values] = STUFF (REPLACE (
(
SELECT value FROM @ t N
WHERE id = A. id
FOR XML AUTO
),' ', ''), 1, 1 ,'')
) N

/* -- Result
Idvalues
---------------------------
1aa, bb
2aaa, bbb, ccc
(2 rows affected)
--*/

-- Various string functions

-- 3.3.1 Use the cursor method to merge strings.
-- Processed data
Create table tb (col1 varchar (10), col2 int)
INSERT tb SELECT 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3

-- Merge processing
-- Define result set table Variables
DECLARE @ t TABLE (col1 varchar (10), col2 varchar (100 ))

-- Define and merge the cursor
DECLARE tb CURSOR LOCAL
FOR
SELECT col1, col2 FROM tb order by col1, col2
DECLARE @ col1_old varchar (10), @ col1 varchar (10), @ col2 int, @ s varchar (100)
OPEN tb
FETCH tb INTO @ col1, @ col2
SELECT @ col1_old = @ col1, @ s =''
WHILE @ FETCH_STATUS = 0
BEGIN
IF @ col1 = @ col1_old
SELECT @ s = @ s + ',' + CAST (@ col2 as varchar)
ELSE
BEGIN
INSERT @ t VALUES (@ col1_old, STUFF (@ s ,''))
SELECT @ s = ',' + CAST (@ col2 as varchar), @ col1_old = @ col1
END
FETCH tb INTO @ col1, @ col2
END
INSERT @ t VALUES (@ col1_old, STUFF (@ s ,''))
CLOSE tb
DEALLOCATE tb
-- Display results and delete test data
SELECT * FROM @ t
Drop table tb
/* -- Result
Col1col2
---------------------
A1, 2
B1, 2, 3
--*/
GO

/* ===================================================== ========= */

-- 3.3.2 use a user-defined function, U.S. space, and an example of string merging with SELECT processing
-- Processed data
Create table tb (col1 varchar (10), col2 int)
INSERT tb SELECT 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3
GO

-- Merge processing functions
Create function dbo. f_str (@ col1 varchar (10 ))
RETURNS varchar (100)
AS
BEGIN
DECLARE @ re varchar (100)
SET @ re =''
SELECT @ re = @ re + ',' + CAST (col2 as varchar)
FROM tb
WHERE col1 = @ col1
RETURN (STUFF (@ re, 1,1 ,''))
END
GO

-- Call a function
SELECT col1, col2 = dbo. f_str (col1) FROM tb group by col1
-- Delete test
Drop table tb
Drop function f_str
/* -- Result
Col1col2
---------------------
A1, 2
B1, 2, 3
--*/
GO
------------------------------------------
-- 3.3.3 example of string merging using temporary tables
-- Processed data
Create table tb (col1 varchar (10), col2 int)
INSERT tb SELECT 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'B', 3

-- Merge processing
SELECT col1, col2 = CAST (col2 as varchar (100 ))
INTO # t FROM tb
Order by col1, col2
DECLARE @ col1 varchar (10), @ col2 varchar (100)
UPDATE # t SET
@ Col2 = case when @ col1 = col1 THEN @ col2 + ',' + col2 ELSE col2 END,
@ Col1 = col1,
Col2 = @ col2
SELECT * FROM # t
/* -- Update the temporary table after processing
Col1col2
-----------------------
A1
A1, 2
B1
B1, 2
B1, 2, 3
--*/
-- Get the final result
SELECT col1, col2 = MAX (col2) FROM # t group by col1
/* -- Result
Col1col2
---------------------
A1, 2
B1, 2, 3
--*/
-- Delete test
Drop table tb, # t
GO

/* ===================================================== ========= */

-- 3.3.4.1 merge of <= 2 records in each group
-- Processed data
Create table tb (col1 varchar (10), col2 int)
INSERT tb SELECT 'A', 1
Union all select 'A', 2
Union all select 'B', 1
Union all select 'B', 2
Union all select 'C', 3

-- Merge processing
SELECT col1,
Col2 = CAST (MIN (col2) as varchar)
+ CASE
When count (*) = 1 then''
ELSE ',' + CAST (MAX (col2) as varchar)
END
FROM tb
Group by col1
Drop table tb
/* -- Result
Col1col2
--------------------
A1, 2
B1, 2
C3
--*/