How to join two rows into just one in SQL Server

I'm trying to write a SQL query where the idea is to join two rows into one.
When performing

select * from caixa


I get the following result:

id money open closed fun type situation
--------------------------------------------------------------------
13 10 08/09/2018 18:55 1 E 1
37 -473 09/09/2018 17:01 1 F 1
40 0 09/09/2018 17:08 1 E 2
41 0 10/09/2018 17:09 1 F 2


The idea is to join the two rows where the situations are the same
getting for example:

money open closed fun type situation
--------------------------------------------------------------------
-473 08/09/2018 18:55 09/09/2018 17:01 1 E 1


money

fun

type

situation

situation

2 Answers
2

You can try to use aggregate function to make it

SELECT MIN(money) as money ,MAX(open) as open,
MAX(closed) as closed,fun,MIN(type) as type,
MIN(situation) as situation
FROM caixa
GROUP BY fun


MAX

NULL

Try joining the table with itself

SELECT O.money as open_money, C.money as closed_money,
O.open as open, C.closed as closed,
O.fun as open_fun, C.fun as closed_fun,
O.type as open_type, C.type as closed_type
FROM caixa O, caixa C
WHERE O.id <> C.id
AND O.situation = C.situation
AND O.open IS NOT NULL
AND C.closed IS NOT NULL


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.