5

I've a table in which data is like this for single user

ID - Number - SubNumber - Name

1 101 201101 Jack
2 101 201102 Jack
3 101 201103 Jack
4 101 201107 Jack
5 101 201111 Jack
6 101 201112 Jack
7 101 201113 Jack
8 101 201161 Jack
9 101 201162 Jack
10 101 201163 Jack
11 101 201164 Jack
12 101 201165 Jack

I want to get records like this without using any kind of loop.

Number - Name - SubNumber

101 Jack (201101-201103, 201107, 201111-201113, 201161-201165)

Currently I'm able to get records in form of this

Number - Name - SubNumber

101 Jack (201101,201102,201103, 201107, 201111.201112,201113, 201161,201162,201163,201164,201165)

Query to get upper result is

SELECT  Number, Name
,STUFF((SELECT ', ' + CAST(SubNumber AS VARCHAR(50)) [text()]
     FROM [Table] 
     WHERE Number= t.Number
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') SubNumber
FROM [Table] t
GROUP BY Number,Name
having Number= '101'

Am totally stuck over here. Any kind of help will be appreciated.

  • regex , group concat – ninjabber Apr 8 '15 at 11:00
  • 1
    The first part of your issue is a "gaps-and-islands" problem. So, the solution could be to first use a gaps-and-islands query and the output with your XML concatenation query. Which version of SQL-Server are you working on? (it's easier in 2012+ versions than previous ones.) – ypercubeᵀᴹ Apr 8 '15 at 11:23
  • 1
    @ninjabber There is no need for regex (there never is ;) and SQL-Server has no GROUP_CONCAT() function (that's why they use FOR XML PATH). – ypercubeᵀᴹ Apr 8 '15 at 11:26
  • 1
    @ypercube oops am using 2005 -_- – Null Pointer Apr 8 '15 at 11:31
  • @ypercube, thanks. Should pay more attention. I do not agree about the regex but lets leave this subject for another time :) – ninjabber Apr 8 '15 at 16:33
3

First add a row_number() value to the mix, because I'm guessing you can't rely on ID to have no gaps.

WITH NumberedRows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Number, Name ORDER BY SubNumber) AS RowNum
  FROM [Table]
)

--now compare RowNum to SubNumber. When the difference changes, there's been a gap in the sequence of SubNumber values.

, Diffs AS (
  SELECT *, SubNumber - RowNum AS TheDiff
  FROM NumberedRows
)
, Ranges AS (
  SELECT Number, Name,
    MIN(SubNumber) AS StartRange, 
    MAX(SubNumber) AS EndRange
  FROM Diffs
  GROUP BY Number, Name, TheDiff
)
, RangeStrings AS (
  SELECT Number, Name, 
    CASE WHEN StartRange = EndRange 
         THEN CAST(StartRange AS VARCHAR(10))
         ELSE CAST(StartRange AS VARCHAR(10)) + '-' + CAST(EndRange AS VARCHAR(10))
    END AS RangeString
  FROM Ranges
)

--And then just do your concatenation as you have already solved. :)

SELECT ...
FROM RangeStrings;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.