CODEWITHSUNDEEP
sqlsql-servert-sql
Akii
Akii

Reputation: 125

Merge rows with same value in a specific column

So I have a table which records the changes of a card number of a user called dbo.card_history:

 |  id  |  name  |  card_no  |  card_state  |        change_time        |
 |  1   |  Ami   |   11111   |       1      |  2018-11-05 21:11:06.117  |

If I modify the card number of this user from another table called dbo.card, dbo.card history will automatically generate additional 2 new rows and will now look like this:

 |  id  |  name  |  card_no  |  card_state  |        change_time        |
 |  1   |  Ami   |   11111   |       1      |  2018-11-05 21:11:06.117  |
 |  2   |  Ami   |   11111   |      13      |  2018-11-05 21:11:21.953  |
 |  3   |  Ami   |   22222   |       1      |  2018-11-05 21:11:21.953  |

Basically it will record the old row but with a card_state of 13 and generate a new row with the update value and a card_state of 1

Additionally if I delete the value of the card it will generate a new row with the record of the old row but with a card_state of 12, so now it will look like this:

 |  id  |  name  |  card_no  |  card_state  |        change_time        |
 |  1   |  Ami   |   11111   |       1      |  2018-11-05 21:11:06.117  |
 |  2   |  Ami   |   11111   |      13      |  2018-11-05 21:11:21.953  |
 |  3   |  Ami   |   22222   |       1      |  2018-11-05 21:11:21.953  |
 |  4   |  Ami   |   22222   |      12      |  2018-11-05 21:16:09.397  |

What I want to do is make a query that will return something like this:

 |  id  |  name  |  card_no  |  card_state  |        change_time        |    old_card_no   |
 |  1   |  Ami   |   11111   |       1      |  2018-11-05 21:11:06.117  |        NULL      |
 |  2   |  Ami   |   22222   |       1      |  2018-11-05 21:11:21.953  |       11111      |
 |  3   |  Ami   |   22222   |      12      |  2018-11-05 21:16:09.397  |        NULL      |

I want to merge rows with the same change_time such that the card_no of rows with card_state of 13 will returned in a new row called old_card_no

I tried a query like this:

SELECT P.id, P.name, P.card_no, P.card_state, P.change_time, 
       MIN(CASE WHEN P.card_state != 1 then P.card_no END) AS old_card_no
FROM [dbo].[card_history] P 
GROUP BY P.pin, P.name, P.last_name, P.card_no, P.change_time, P.card_state

this will return:

 |  id  |  name  |  card_no  |  card_state  |        change_time        |    old_card_no   |
 |  1   |  Ami   |   11111   |       1      |  2018-11-05 21:11:06.117  |        NULL      |
 |  2   |  Ami   |   11111   |      13      |  2018-11-05 21:11:21.953  |       11111      |
 |  3   |  Ami   |   22222   |       1      |  2018-11-05 21:11:21.953  |        NULL      |
 |  4   |  Ami   |   22222   |      12      |  2018-11-05 21:16:09.397  |       22222      |

Sorry, I'm a quite a newbie, and I realize I must be missing something very obvious here, but I've been stuck at this for quite a while, need help ;m;

Upvotes: 2

Views: 4428

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

One fairly simple way is to use a subquery:

SELECT id, name, card_no, card_state, change_time,
       (select card_no
        from [dbo].[card_history] t1
        where card_state = 13
        and t0.name = t1.name
        and t0.change_time = t1.change_time
        ) as old_card_no
FROM [dbo].[card_history] t0
WHERE card_state != 13

Upvotes: 1

t3hjeff
t3hjeff

Reputation: 23

If i heard correctly you want to merge 2 rows which are matched by certain column name, for that you can use GROUP BY with Max or Min like: "SELECT ex1,ex2,Max(change_time) as change_time,..... FROM table GROUP BY something"

Upvotes: 0

Related Questions