Get Specific data from database without loop

Question

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.

you said tat you are able to getting all 'sub number' then use **where id not in (2,6,9,10,11)** — koushik veldanda, Apr 08 '15 at 10:06
@Kyborek please check my edit. I've updated my question with query. — Naila Akbar, Apr 08 '15 at 10:26
You first need an existing 'tally table' that contains 65 records from 201101 to 201165. Then you need to process your existing records so that they have a usable 'from' and 'to' period, then join to the tally table using BETWEEN. First do some research on tally tables, set one up, then get back and I can help further. Alternatively someone will probably just post the entire solution soon. — Nick.Mc, Apr 08 '15 at 10:29
@Nick.McDermaid thanks for the hint. I do work on it. Thankyou — Naila Akbar, Apr 08 '15 at 10:52
Actually, based on that last sample data all you need is a split function like this:http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql... you don't even need a tally table. — Nick.Mc, Apr 08 '15 at 10:55

Giorgi Nakeuri · Answer 1 · 2015-04-08T11:24:51.530

Try this. For MS SQL Server 2012+:

DECLARE @t TABLE
    (
      ID INT ,
      Number INT ,
      Code INT
    )

INSERT  INTO @t
VALUES  ( 1, 201101, 101 ),
        ( 2, 201102, 101 ),
        ( 3, 201103, 101 ),
        ( 4, 201107, 101 ),
        ( 5, 201111, 101 ),
        ( 6, 201112, 101 ),
        ( 7, 201113, 101 ),
        ( 8, 201161, 101 ),
        ( 9, 201162, 101 ),
        ( 10, 201163, 101 ),
        ( 11, 201164, 101 ),
        ( 12, 201165, 101 ),
        ( 13, 201166, 102 ),
        ( 14, 201169, 102 ),
        ( 15, 201175, 102 ),
        ( 16, 201176, 102 ),
        ( 17, 201177, 102 );

WITH    cte1
          AS ( SELECT   * ,
                        CASE WHEN number
                                  - LAG(Number) OVER ( PARTITION BY Code ORDER BY ID ) = 1
                             THEN 0
                             ELSE 1
                        END AS lg
               FROM     @t
             ),
        cte2
          AS ( SELECT   * ,
                        SUM(lg) OVER ( PARTITION BY Code ORDER BY ID ) AS s
               FROM     cte1
             ),
        cte3
          AS ( SELECT   * ,
                        MIN(Number) OVER ( PARTITION BY Code, s ) AS mi ,
                        MAX(Number) OVER ( PARTITION BY Code, s ) AS ma
               FROM     cte2
             ),
        cte4
          AS ( SELECT   Code ,
                        mi ,
                        ma
               FROM     cte3
               GROUP BY Code ,
                        mi ,
                        ma
             )
    SELECT  code ,
            STUFF((SELECT   ', '
                            + CASE WHEN mi <> ma
                                   THEN CAST(mi AS NVARCHAR(MAX)) + '-'
                                        + CAST(ma AS NVARCHAR(MAX))
                                   ELSE CAST(mi AS NVARCHAR(MAX))
                              END
                   FROM     cte4
                   WHERE    Code = t.Code
            FOR   XML PATH('') ,
                      TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Number
    FROM    cte4 t
    GROUP BY Code

Output:

code    Number
101     201101-201103, 201107, 201111-201113, 201161-201165
102     201166, 201169, 201175-201177

score 0 · Answer 2 · answered Apr 08 '15 at 11:24

First you should select the start and the finish for each interval (see CT - view) then form a strings using format START-FINISH for each interval (see CT2 view). Here you should also handle the case when start=finish (see CASE statement). Then use your query to group and concatenate strings for each Name.

WITH CT AS
(
 SELECT Id,Number, SubNumber as StartNum, Null As EndNumber, Name 
  FROM T
  WHERE NOT EXISTS (SELECT SubNumber FROM T as T1 
                    WHERE T1.Number=T.Number AND T1.SubNumber+1=T.Subnumber)
 UNION 
 SELECT Id,Number,Null as StartNum, SubNumber As EndNumber, Name 
  FROM T
  WHERE NOT EXISTS (SELECT SubNumber FROM T as T1 
                    WHERE T1.Number=T.Number AND T1.SubNumber-1=T.Subnumber)
),
 CT2 AS
 (
   SELECT ID,Number,
          CAST(StartNum AS VARCHAR(50)) 
          + (SELECT TOP 1 
                CASE WHEN T3.EndNumber = CT.StartNum 
                     THEN '' 
                     ELSE '-' +CAST(T3.EndNumber AS VARCHAR(50)) 
                END 
              FROM CT as T3 
                WHERE T3.Number=CT.Number 
                      AND T3.EndNumber>=CT.StartNum
             ORDER BY EndNumber) as SubNumber  
           ,Name
   FROM CT 
    WHERE EndNumber IS NULL
  )

SELECT  Number, Name
,STUFF((SELECT ', ' + SubNumber [text()]
     FROM CT2 
     WHERE Number= t.Number
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') SubNumber
FROM CT2 t
GROUP BY Number,Name
HAVING Number= '101'

SQLFiddle demo

Get Specific data from database without loop

2 Answers2