locked
Help To solve the following issue RRS feed

  • Question

  • declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    ('4886',	'4886'),
    ('4887',	'4888'),
    ('231387',	'231388'),
    ('231389',	'231389'),
    ('231390',	'231391'),
    ('231408',	'231410'),
    ('231414',	'231415'),
    ('231416',	'231418'),
    ('231419',	'231421'),
    ('231595',	'231598'),
    ('231599',	'231602'),
    ('231603',	'231607'),
    ('231616',	'231619'),
    ('231620',	'231623'),
    ('231624',	'231625'),
    ('231626',	'231630'),
    ('231631',	'231634'),
    ('231635',	'231638'),
    ('231639',	'231641'),
    ('231642',	'231646')
    


    ebro

    Wednesday, February 18, 2015 5:21 PM

Answers

  • Hey Charlie,

    The OP wants to group contigious lines together into a single row.

    In his example (not in the example data) rows '0001', '0004' and '0005','0007' become a single row, as the secondID is sequential with the next firstID.

    I made a couple of changes to my original example:

    declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    ('4886',	'4886'),('4887',	'4888'),('231387',	'231388'),('231389',	'231389'),('231390',	'231391'),('231408',	'231410'),('231414',	'231415'),('231416',	'231418'),
    ('231419',	'231421'),('231595',	'231598'),('231599',	'231602'),('231603',	'231607'),('231616',	'231619'),('231620',	'231623'),('231624',	'231625'),('231626',	'231630'),
    ('231631',	'231634'),('231635',	'231638'),('231639',	'231641'),('231642',	'231646'),
    ('0001','0004'),('0005','0007'),('0010','0016'),('0017','0019'),('0021','0025'),('0026','0029'),('0030','0033')
    
    ;WITH base AS (
    SELECT ID, FirstID, SecondID, LEAD(FirstID) OVER (ORDER BY ID) AS nextFirstID, 
     CASE WHEN LEAD(FirstID) OVER (ORDER BY ID) = SecondID + 1 THEN 1 ELSE 0 END contigious
      FROM @t
    ), rCTE AS (
    SELECT b.ID, b.firstID, b.SecondID, b.contigious, 0 as count
      FROM base b
     WHERE contigious = 0
    UNION ALL
    SELECT r.ID, b.firstID, r.SecondID, b.contigious, r.count + 1 
      FROM rCTE r
        INNER JOIN base b
    	  ON r.firstID = b.nextFirstID
    	  AND b.contigious = 1
     )
    
     SELECT id, min(firstID) AS firstID, max(SecondID) AS secondID, COUNT(*) AS entries
       FROM rCTE
    group by ID
    

    • Proposed as answer by Charlie Liao Monday, March 2, 2015 2:41 PM
    • Marked as answer by Charlie Liao Monday, March 9, 2015 1:57 AM
    Thursday, February 19, 2015 2:36 PM

All replies

  • Try this out:

    declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    ('4886',	'4886'),('4887',	'4888'),('231387',	'231388'),('231389',	'231389'),('231390',	'231391'),('231408',	'231410'),('231414',	'231415'),('231416',	'231418'),
    ('231419',	'231421'),('231595',	'231598'),('231599',	'231602'),('231603',	'231607'),('231616',	'231619'),('231620',	'231623'),('231624',	'231625'),('231626',	'231630'),
    ('231631',	'231634'),('231635',	'231638'),('231639',	'231641'),('231642',	'231646')
    
    ;WITH base AS (
    SELECT ID, FirstID, SecondID, LEAD(FirstID) OVER (ORDER BY ID) AS nextFirstID, LAG(SecondID) OVER (ORDER BY ID) AS prevLastID,
     CASE WHEN LEAD(FirstID) OVER (ORDER BY ID) = SecondID + 1 THEN 1 ELSE 0 END contigious
      FROM @t
    ), rCTE AS (
    SELECT b.ID, b.firstID, b.SecondID, b.contigious, 0 as count
      FROM base b
     WHERE contigious = 1
    UNION ALL
    SELECT r.ID, r.firstID, b.SecondID, b.contigious, r.count + 1 
      FROM rCTE r
        INNER JOIN base b
    	  ON r.ID + 1 = b.ID
    	  AND b.contigious = 0
    	  AND r.contigious = 1
     )
    
     SELECT id, min(firstID) AS firstID, max(SecondID) AS secondID, COUNT(*) AS entries
       FROM rCTE
    group by ID
    

    Wednesday, February 18, 2015 5:51 PM
  • This is a typical scenario where quirky update can be applied

    see below illustration

    declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    ('4886',	'4886'),
    ('4887',	'4888'),
    ('231387',	'231388'),
    ('231389',	'231389'),
    ('231390',	'231391'),
    ('231408',	'231410'),
    ('231414',	'231415'),
    ('231416',	'231418'),
    ('231419',	'231421'),
    ('231595',	'231598'),
    ('231599',	'231602'),
    ('231603',	'231607'),
    ('231616',	'231619'),
    ('231620',	'231623'),
    ('231624',	'231625'),
    ('231626',	'231630'),
    ('231631',	'231634'),
    ('231635',	'231638'),
    ('231639',	'231641'),
    ('231642',	'231646')
    
    SELECT *,CAST(NULL AS int) AS StartID
    INTO #Temp
    FROM @t
    
    
    
    CREATE CLUSTERED INDEX IDX_Clust ON #Temp(FirstID,SecondID)
    
    
    DECLARE @FirstID int,@SecondID int
    
    SELECT TOP 1 @FirstID = FirstID,@SecondID = SecondID
    FROM #Temp
    ORDER BY FirstID,SecondID
    
    
    UPDATE t
    SET @FirstID = t.StartID = CASE WHEN FirstID = @SecondID+1  AND SecondID >@SecondID THEN @FirstID ELSE FirstID END,
    @SecondID = t.SecondID
    FROM #Temp t WITH (TABLOCKX)
    OPTION (MAXDOP 1)
    
    SELECT MIN(FirstID) AS FirstID,MAX(SecondID) AS SecondID
    FROM #Temp
    GROUP BY StartID
    DROP TABLE #Temp

    Also see explanation here

    http://visakhm.blogspot.in/2010/03/using-quirky-updates-to-develop-well.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 18, 2015 6:32 PM
  • declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    (1,	4),
    (5,	7),
    (10, 16),
    (17, 19) 
    
    ;with mycte as  (
    select * ,    Case WHEN lead(FirstID) Over(Order by ID)-SecondID =1 Then 1  Else 0 End  as delta
     from  @t  )
    
     ,mycte2 as (
     select ID, FirstID, SecondID, Sum(delta) Over(oRDER BY id) grp  
     from mycte)
     
     
     Select  MIN(FirstID) FirstID, Max(SecondID) SecondID 
     from mycte2
     Group by grp
     Order by FirstID

    • Proposed as answer by Charlie Liao Thursday, February 19, 2015 2:42 AM
    Wednesday, February 18, 2015 8:04 PM
  • Hi Ebro,

    In your scenario, the original table looks like
    ID    FirstID    SecindID
    1      0001      0004
    1      0005      0007
    1      0010      0016
    1      0017      0019

    the expected table looks like
    ID    FirstID    SecindID
    1      0001      0007
    1      00010    0019

    Please elaborate the logical that change the original table to expected table, so that we can make further analysis.

    By the way, Jingyang Li's query can give you expected results.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, February 19, 2015 2:53 AM
  • Hey Charlie,

    The OP wants to group contigious lines together into a single row.

    In his example (not in the example data) rows '0001', '0004' and '0005','0007' become a single row, as the secondID is sequential with the next firstID.

    I made a couple of changes to my original example:

    declare @t table  (ID int identity(1,1), FirstID int, SecondID int)
    insert into @t values 
    ('4886',	'4886'),('4887',	'4888'),('231387',	'231388'),('231389',	'231389'),('231390',	'231391'),('231408',	'231410'),('231414',	'231415'),('231416',	'231418'),
    ('231419',	'231421'),('231595',	'231598'),('231599',	'231602'),('231603',	'231607'),('231616',	'231619'),('231620',	'231623'),('231624',	'231625'),('231626',	'231630'),
    ('231631',	'231634'),('231635',	'231638'),('231639',	'231641'),('231642',	'231646'),
    ('0001','0004'),('0005','0007'),('0010','0016'),('0017','0019'),('0021','0025'),('0026','0029'),('0030','0033')
    
    ;WITH base AS (
    SELECT ID, FirstID, SecondID, LEAD(FirstID) OVER (ORDER BY ID) AS nextFirstID, 
     CASE WHEN LEAD(FirstID) OVER (ORDER BY ID) = SecondID + 1 THEN 1 ELSE 0 END contigious
      FROM @t
    ), rCTE AS (
    SELECT b.ID, b.firstID, b.SecondID, b.contigious, 0 as count
      FROM base b
     WHERE contigious = 0
    UNION ALL
    SELECT r.ID, b.firstID, r.SecondID, b.contigious, r.count + 1 
      FROM rCTE r
        INNER JOIN base b
    	  ON r.firstID = b.nextFirstID
    	  AND b.contigious = 1
     )
    
     SELECT id, min(firstID) AS firstID, max(SecondID) AS secondID, COUNT(*) AS entries
       FROM rCTE
    group by ID
    

    • Proposed as answer by Charlie Liao Monday, March 2, 2015 2:41 PM
    • Marked as answer by Charlie Liao Monday, March 9, 2015 1:57 AM
    Thursday, February 19, 2015 2:36 PM

  • select a.FirstID as firstiss,b.SecondID as second from 
    ##t a inner join ##t b on a.ID=b.ID-1 where a.ID %2=1
    • Edited by tusharshinde Thursday, February 19, 2015 2:47 PM
    Thursday, February 19, 2015 2:46 PM