1

I have a select query that returns these results:

Transaction  Type   Value
-----------  ----   -----
1            A      Null
1            A      Null
1            B      1234
2            A      Null
2            A      Null
2            B      4321

How would i form it so the Null values from type A get replace by the value in type B? To get this result:

Transaction  Type  Value
-----------  ----  -----
1            A     1234
1            A     1234
1            B     1234
2            A     4321
2            A     4321
2            B     4321
dnok
  • 13
  • 2
  • Hi @dnok, show us the query. Also please tell us in which database do you work, is it Oracle, SQLServer, MySQL ? Thanks! Any information is helpful ! – VBoka Nov 03 '19 at 21:44
  • Welcome to StackOverflow, @dnok! Just for clarification, you want the value in type B of the same Transaction? I am not sure if in SQL you can update a value of one entry in a table with the value of another entry in the same table. But you definitely can do it if you split them into to tables: one for type A, another for type B transactions. Then join them and update .. set. Here is [an example](https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match). – xealits Nov 03 '19 at 22:32
  • Thanks for the quick replies. It is Microsoft sql server and the query is just a basic select * from table. Xealits yes I'd like the values of type B in the same transaction for the null values. – dnok Nov 03 '19 at 22:40

1 Answers1

1

You can use window functions:

select transaction, type, value,
       max(case when type = 'b' then value end) over (partition by transaction) as new_value
from t;

If you just want the non-NULL value for the transaction:

select transaction, type, value,
       max(value) over (partition by transaction) as new_value
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786