A SQL statement-CodePudding

 
Insert into Temp (' A1000001) 
Insert into Temp (' A1000002) 
Insert into Temp (' A1000003) 
Insert into Temp (' A1000004) 
Insert into Temp (' A1000006) 
Insert into Temp (' A1000007) 
Insert into Temp (' A1000009) 
Insert into Temp (' A10000010) 
Insert into Temp (' A10000011) 
Insert into Temp (' A10000012)

As above data, I want to have the output of the collection of data in a string, if there is broken, use, said that, if not broken, show the starting number - terminated,
The above data output should be as follows: A1000001 - A1000004 A1000006 - A1000007 A1000009 - A1000012

CodePudding user response:

 
The CREATE TABLE # Temp 
(
Id INT IDENTITY (1, 1), 
Val VARCHAR (50) 
) 
INSERT into # Temp (val) values (' A1000001) 
Insert into # Temp (val) values (' A1000002) 
Insert into # Temp (val) values (' A1000003) 
Insert into # Temp (val) values (' A1000004) 
Insert into # Temp (val) values (' A1000006) 
Insert into # Temp (val) values (' A1000009) 
Insert into # Temp (val) values (' A10000010) 
Insert into # Temp (val) values (' A10000011) 
Insert into # Temp (val) values (' A10000012) 

The SELECT STUFF (
(
SELECT the ', '+ mi + CASE WHEN mi=mx THEN' else '-' + mx END FROM 
(
The SELECT MIN (val) AS the mi, MAX (val) AS mx, MIN (id) AS id FROM 
(
SELECT *, id - CONVERT (INT, the SUBSTRING (val, 3100)) AS x 
The FROM # Temp 
) a 
GROUP BY a.x 
B) 
The ORDER BY id 
FOR XML PATH (' ') 
), 1, 1 ') 

DROP TABLE # Temp

CodePudding user response:

 
- I'm dizzy, you of the data bits is not the same as 
The CREATE TABLE # Temp 
(
Id INT IDENTITY (1, 1), 
Val VARCHAR (50) 
) 
INSERT into # Temp (val) values (' A10000001) 
Insert into # Temp (val) values (' A10000002) 
Insert into # Temp (val) values (' A10000003) 
Insert into # Temp (val) values (' A10000004) 
Insert into # Temp (val) values (' A10000006) 
Insert into # Temp (val) values (' A10000009) 
Insert into # Temp (val) values (' A10000010) 
Insert into # Temp (val) values (' A10000011) 
Insert into # Temp (val) values (' A10000012) 

The SELECT STUFF (
(
SELECT the ', '+ mi + CASE WHEN mi=mx THEN' else '-' + mx END FROM 
(
The SELECT MIN (val) AS the mi, MAX (val) AS mx, MIN (id) AS id FROM 
(
SELECT *, id - CONVERT (INT, the SUBSTRING (val, 3100)) AS x 
The FROM # Temp 
) a 
GROUP BY a.x 
B) 
The ORDER BY id 
FOR XML PATH (' ') 
), 1, 1 ') 

DROP TABLE # Temp

CodePudding user response:

The

reference 1/f, sleet response:

 
The CREATE TABLE # Temp 
(
Id INT IDENTITY (1, 1), 
Val VARCHAR (50) 
) 
INSERT into # Temp (val) values (' A1000001) 
Insert into # Temp (val) values (' A1000002) 
Insert into # Temp (val) values (' A1000003) 
Insert into # Temp (val) values (' A1000004) 
Insert into # Temp (val) values (' A1000006) 
Insert into # Temp (val) values (' A1000009) 
Insert into # Temp (val) values (' A10000010) 
Insert into # Temp (val) values (' A10000011) 
Insert into # Temp (val) values (' A10000012) 

The SELECT STUFF (
(
SELECT the ', '+ mi + CASE WHEN mi=mx THEN' else '-' + mx END FROM 
(
The SELECT MIN (val) AS the mi, MAX (val) AS mx, MIN (id) AS id FROM 
(
SELECT *, id - CONVERT (INT, the SUBSTRING (val, 3100)) AS x 
The FROM # Temp 
) a 
GROUP BY a.x 
B) 
The ORDER BY id 
FOR XML PATH (' ') 
), 1, 1 ') 

DROP TABLE # Temp

The great god, and to kneel

CodePudding user response:

The

reference 1/f, sleet response:

 
The CREATE TABLE # Temp 
(
Id INT IDENTITY (1, 1), 
Val VARCHAR (50) 
) 
INSERT into # Temp (val) values (' A1000001) 
Insert into # Temp (val) values (' A1000002) 
Insert into # Temp (val) values (' A1000003) 
Insert into # Temp (val) values (' A1000004) 
Insert into # Temp (val) values (' A1000006) 
Insert into # Temp (val) values (' A1000009) 
Insert into # Temp (val) values (' A10000010) 
Insert into # Temp (val) values (' A10000011) 
Insert into # Temp (val) values (' A10000012) 

The SELECT STUFF (
(
SELECT the ', '+ mi + CASE WHEN mi=mx THEN' else '-' + mx END FROM 
(
The SELECT MIN (val) AS the mi, MAX (val) AS mx, MIN (id) AS id FROM 
(
SELECT *, id - CONVERT (INT, the SUBSTRING (val, 3100)) AS x 
The FROM # Temp 
) a 
GROUP BY a.x 
B) 
The ORDER BY id 
FOR XML PATH (' ') 
), 1, 1 ') 

DROP TABLE # Temp

There is a flaw is that there is no do val sorting, if order messing with no way out

CodePudding user response:

 
- and no order is not afraid of, the key is your data in no, are you sure, I do is look at the way in to 
The CREATE TABLE # Temp 
(
Val VARCHAR (50) 
) 
Insert into # Temp (val) values (' A10000003) 
INSERT into # Temp (val) values (' A10000001) 
Insert into # Temp (val) values (' A10000002) 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull