In a SQL query, to bring row values in a single row, in the field

Question

In a SQL query, to bring row values in a single row, in the field

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I need to make a query that brings values of 2 rows into a single Ex:

id     | valor   |  banco
000001 | 1000,00 |  caixa
000001 | 1000,00 |  bradesco

I need it to look like this:

id     |  valor   | banco 1 | banco 2
000001 |  1000,00 | caixa   | bradesco

In case I need to return values in a row separating by fields.

sql sql-server

asked by anonymous 02.08.2018 / 21:45

2 answers

More or less a Select in array Listbox items out of range

score 3 · Answer 1

I need to do a query that brings values of 2 rows into one

Assuming there is a seq column that indicates the order of the rows, by id , and that id identifies each pair in a unique way, here's a suggestion:

-- código #1
SELECT id, valor,
       max(case when seq = 1 then banco end) as [banco 1],
       max(case when seq = 2 then banco end) as [banco 2]
  from nome_tabela
  group by id, valor;

But if the seq column does not exist and both place the order in which the bank names are listed, here's another option:

-- código #2
with tabela_seq as (
SELECT *,
       seq= row_number() over (partition by id order by (SELECT 0))
  from nome_tabela  
)
SELECT id, valor,
       max(case when seq = 1 then banco end) as [banco 1],
       max(case when seq = 2 then banco end) as [banco 2]
  from tabela_seq
  group by id, valor;

In the two above codes you should replace nome_tabela with the name of the table containing the data.

I will improve my question, (...)
The RECPAG field defines the bank that pays = p and the one that receives = R,

Here is code # 1 updated, considering the additional information:

-- código #1 v2
SELECT NUM, VALOR, DATA,
       max(case when RECPAG = 'P' then BANCO end) as [BANCO P],
       max(case when RECPAG = 'R' then BANCO end) as [BANCO R],
       TIPO
  from nome_tabela
  group NUM, VALOR, DATA, TIPO;

score 0 · Answer 2

I will improve my question,

I need to make a query on SQL server EX:

00001 | 990,00 | 03042018 | P | SANTANDER | TR

00001 | 990,00 | 03042018 | R | BRADESCO | TR

00002 | 100,00 | 04052018 | P | BRAZIL | TR

00002 | 100,00 | 04052018 | R | BOX | TR

00003 | 200,00 | 05052018 | P | SANTANDER | TR

00003 | 200,00 | 05052018 | R | BOX | TR

The RECPAG field defines the bank that pays = p and the one that receives = R,

I need to unify in an EX line:

00001 | 990,00 | 03042018 | SANTANDER | BRADESCO | TR

00002 | 100,00 | 04052018 | BRAZIL | BOX | TR

00003 | 200,00 | 05052018 | SANTANDER | BOX | TR