CODEWITHSUNDEEP
sqlsql-server
User2017
User2017

Reputation: 11

Replace consecutive identical row occurences to single row based on same Id - SQL Server

I am trying to reduce the consecutive identical rows within the same Id to one single row. I tried duplication but then it replaces all non-consecutive identical occurrences within the same Id to one single row. Also, the order of the message is important. The input and the desired output is shown below. Is there any way to achieve this desired result?

Thanks

Input data

Id   Result   Message
----------------------
1     0        a 
1     0        p 
1     0        p
1     0        p
1     0        d
1     0        p 
1     0        p 
1     0        f
1     0        p
2     1        a
2     1        a
2     1        a
2     1        f
2     1        h
2     1        b
2     1        b
3     0        d
3     0        d
3     0        d
3     0        c
3     0        c

Desired output

Id   Result   Message
----------------------
1     0        a 
1     0        p 
1     0        d
1     0        p  
1     0        f
1     0        p
2     1        a
2     1        f
2     1        h
2     1        b
3     0        d
3     0        c

Upvotes: 1

Views: 93

Answers (2)

KDB
KDB

Reputation: 19

I guess you're looking for Group by?

SELECT col1, col2, col3 FROM Table GROUP BY col1, col2, col3;

The order of the result will be the order of the columns you pass.

Upvotes: 0

Adam
Adam

Reputation: 2440

Taking @GordonLinoff's comment into consideration, if you were to include a column which specified the order in which you wanted the rows looked at, for example,

Id   Result   Message   Order
1     0        a         1
1     0        p         2 
1     0        p         2
1     0        p         2
1     0        d         3
1     0        p         4
1     0        p         4
1     0        f         5
1     0        p         6
2     1        a         7
2     1        a         7
2     1        a         7
2     1        f         8
2     1        h         9
2     1        b        10
2     1        b        10
3     0        d        11
3     0        d        11
3     0        d        11
3     0        c        12
3     0        c        12

Then you could easily obtain the desired result with the following query:

SELECT distinct Id, Result, Message, Order
FROM Table_A

OUPUT:

Id   Result   Message   Order
1     0        a         1
1     0        p         2 
1     0        d         3
1     0        p         4
1     0        f         5
1     0        p         6
2     1        a         7
2     1        f         8
2     1        h         9
2     1        b        10
3     0        d        11
3     0        c        12

Upvotes: 1

Related Questions