Post reply

split range from table

  • January 30, 2014 at 7:01 am

    #396196

    Hi guys, i'm italian, excuse me for my bad english first.

    Is possible to obtain result like this

    from | to | idutente

    1 3 1

    5 8 1

    9 10 2

    from table like this

    CREATE TABLE [dbo].[splittest](

    [numero] [int] NOT NULL,

    [idUtente] [int] NOT NULL,

    CONSTRAINT [PK_splittest] PRIMARY KEY CLUSTERED

    (

    [numero] ASC,

    [idUtente] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    and this values

    insert into splittest(numero, idutente) values (1,1)

    insert into splittest(numero, idutente) values (2,1)

    insert into splittest(numero, idutente) values (3,1)

    insert into splittest(numero, idutente) values (5,1)

    insert into splittest(numero, idutente) values (6,1)

    insert into splittest(numero, idutente) values (7,1)

    insert into splittest(numero, idutente) values (8,1)

    insert into splittest(numero, idutente) values (9,2)

    insert into splittest(numero, idutente) values (10,2)

    with 1 simple query, no cursors ?

    thank you very much. bye

  • January 30, 2014 at 8:41 am

    #1684702

    Hi Alessandro,

    Thank you for posting your sample data with DDL and insert statements.

    This solution might solve your problem, at least it works as expected on the sample data.

    WITH CTE AS(

    SELECT *,

    numero - ROW_NUMBER() OVER( ORDER BY numero) AS grouper

    FROM splittest

    )

    SELECT MIN(numero),

    MAX(numero),

    idUtente

    FROM CTE

    GROUP BY grouper, idUtente

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 30, 2014 at 9:10 am

    #1684723

    Wow ! works perfectly, great solution.

    Amazing

    Thank's Luis !

  • January 30, 2014 at 9:25 am

    #1684731

    You're welcome. It was fun when I found numero and thought it was spanish but it was really italian. 😀

    If you have any questions on how does it work, feel free to ask. You need to understand it before using it in production.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 30, 2014 at 12:48 pm

    #1684819

    thank's Luis, cte is "the key" for this problem.

    you have found a clever method to subtract ROW_NUMBER() from "numero" 🙂

    after this, it's easy.

    I've used a few times cte

    thank's ! Bye

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply