User questions: -------------------------------------- TEST1 table ID START END1 1 52 6 103 21 254 26 305 51 60 Hope results: String: 1-10, 21-30, 51-60 -------------------------- ------------ Solution to the variable splic string: - Construction Table: CREATE TABLE TEST1 (ID INT, START INT, [END] INT) - Add Test Data: Insert Test1 SELECT 1,1,5 Union SELECT 2,6,10 Union SELECT 3,21,25 Union SELECT 4,26,30 Union SELECT 5,51,60
- Establish a string of splicing strings: Create Procedure Proa asbegin
Declare @s varchar (8000) - The corresponding value of the first line in the table in the variable, CAST (MIN (START) -1 here is to correctly connect to the first line in the full table query. Note If the water number starts from 0, '-' To replace other characters such as " ", the last select @S, then change back to '-', the specific statement is: select replace (@S, ' ',' - '): SELECT @ s = CAST (MIN (START) AS VARCHAR (10)) ' - ' Cast (min (start) -1 as varchar (10)) from test1
- Sequer query and splicing strings: select @ s = left (@ s, len (@S) -charindex ('-', reverse (@s)) 1) Case When Cast (Right (@ s, charIndex ('- " ) -1) ',' CAST (START As Varchar (10)) '-' CAST ([end] as varchar (10)) endfrom test1order by start - return Results: SELECT @S
END - Delete Test Table: Drop Table Test1
