Examples of various string merge processing.

--Examples of various string merge processing. sql
SQL code--various string sub functions

--3.3.1 example of string merging with cursor method.
--Processing of data
CREATE TABLE TB (col1 varchar (), 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

--Merger processing
--Define the result set table variable
DECLARE @t TABLE (col1 varchar), col2 varchar (100))

--Define the cursor and merge with it
DECLARE TB CURSOR LOCAL
For
SELECT col1,col2 from TB ORDER by col1,col2
DECLARE @col1_old varchar (@col1 varchar), @col2 int,@s varchar (100)
OPEN TB
FETCH TB into @col1, @col2
SELECT @[email protected],@s= "
While @ @FETCH_STATUS =0
BEGIN
IF @[email Protected]_old
SELECT @[email protected]+ ', ' +cast (@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES (@col1_old, STUFF (@s,1,1, "))
SELECT @s= ', ' +cast (@col2 as varchar), @[email protected]
END
FETCH TB into @col1, @col2
END
INSERT @t VALUES (@col1_old, STUFF (@s,1,1, "))
CLOSE TB
Deallocate TB
--Show results and delete test data
SELECT * from @t
DROP TABLE TB
/*--Results
Col1 col2
---------- -----------
A
B-A
--*/
Go

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

--3.3.2 Example of string merge processing with select processing using user-defined functions
--Processing of data
CREATE TABLE TB (col1 varchar (), 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 function
CREATE FUNCTION dbo.f_str (@col1 varchar (10))
RETURNS varchar (100)
As
BEGIN
DECLARE @re varchar (100)
SET @re = ' '
SELECT @[email protected]+ ', ' +cast (col2 as varchar)
From TB
WHERE [email protected]
RETURN (STUFF (@re, 1, 1, "))
END
GO

--Call function
SELECT Col1,col2=dbo.f_str (col1) from TB GROUP by col1
--Delete test
DROP TABLE TB
DROP FUNCTION F_str
/*--Results
Col1 col2
---------- -----------
A
B-A
--*/
GO

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

--3.3.3 Example of string merge processing using temporal tables
--Processing of data
CREATE TABLE TB (col1 varchar (), 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

--Merger processing
SELECT col1,col2=cast (col2 as varchar (100))
into #t from TB
ORDER by Col1,col2
DECLARE @col1 varchar (), @col2 varchar (100)
UPDATE #t SET
@col2 =case when @col1 =col1 then @col2 + ', ' +col2 ELSE col2 END,
@col1 =col1,
[email protected]
SELECT * from #t
/*--updating a processed temporary table
Col1 col2
---------- -------------
A 1
A
B 1
b
B-A
--*/
--Get the final result
SELECT Col1,col2=max (col2) from #t GROUP by col1
/*--Results
Col1 col2
---------- -----------
A
B-A
--*/
--Delete test
DROP TABLE TB, #t
GO

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

--3.3.4.1 merge of <=2 records per group
--Processing of data
CREATE TABLE TB (col1 varchar (), 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
/*--results
col1        col2      
--------------------
A          
b          
c          3
--*/

--3.3.4.2 merge of <=3 records per group
--Processing of data
CREATE TABLE TB (col1 varchar (), 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
UNION all SELECT ' C ', 3

--Merger processing
SELECT col1,
Col2=cast (MIN (col2) as varchar)
+case
When COUNT (*) =3 then ', '
+cast ((SELECT col2 from TB WHERE col1=a.col1 and col2 not in (MAX (a.col2), MIN (a.col2)) as varchar)
ELSE '
END
+case
When COUNT (*) >=2 Then ', ' +cast (MAX (col2) as varchar)
ELSE '
END
From TB A
GROUP by col1
DROP TABLE TB
/*--Results
Col1 col2
---------- ------------
A
B-A
C 3
--*/
GO

Examples of various string merge processing.