CODEWITHSUNDEEP
sqlsql-server
SQLLearner
SQLLearner

Reputation: 79

SQL: Collapse sequential Data in to one row

I am trying to Collapse sequential data in to one group. For Example :In below City1 data should show 2 rows.

Please help here.

 CREATE TABLE #temp
    (id INT NOT NULL IDENTITY(1, 1) ,
    location1 VARCHAR(50)
    )


INSERT INTO #temp VALUES ('City1')
INSERT INTO #temp VALUES ('City2')
INSERT INTO #temp VALUES ('City1')
INSERT INTO #temp VALUES ('City1')
INSERT INTO #temp VALUES ('City2')
INSERT INTO #temp VALUES ('City2')

SELECT * FROM #temp

Expected Output:

City1  1
city2  2
city1  3
city2   4

Upvotes: 4

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think the most direct way to get what you want uses lag(). You seem to want where the changes occur:

select row_number() over (order by id) as new_id, location1
from (select t.*, lag(location1) over (order by id) as prev_location1
      from #temp t
     ) t
where prev_location1 is null or prev_location1 <> location1;

Here is a rextester showing the solution.

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please use like this. (Assuming you are using SQL 2012+)

solution 1

select location1, x1 from 
(
    select * , ROW_NUMBER() OVER (PARTITION BY x1 order by Id) rnk from 
    (
        select * ,sum(p) over(order by id)+1 x1 from 
        (
            select * , case when location1 =  ISNULL(lag(location1) over (order by id),location1) then 0 else 1 end p   
            from temp2 
        )x
    )k
)p where rnk  = 1

OUTPUT

location1            x1
-------------------- -----------
City1                1
City2                2
City1                3
City2                4

(4 rows affected)

Upvotes: 1

Related Questions