In an SQL query, bring line values in a single line, field

Asked

Viewed 730 times

1

I need to make one query that brings values of 2 lines in a single Ex:

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

I need you to leave like this:

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

In the precise case return the values in a line separated by fields.

sql sql-server

  • Which bank? Mysql, Sqlserver, Postgresql, Oracle? Each has a different way of doing what Cvoce wants. Specify better.

  • Sql Server 2014

  • it doesn’t make much sense to bring balances in columns, but ok... and the name of it is pivot table, try some of the questions already asked: https://answall.com/search?q=%5Bsql-server%5D+pivot+table

  • 3

    Possible duplicate of Turn rows into a select column - PIVOT - SQL Server

  • 2

    Look, it shouldn’t be hard to do what you want. The problem is that as you didn’t explain it properly, you can’t know what you want and so you can’t answer the question. I even tried to come up with an answer, but there’s a lot missing from your question to make it possible. Are banks always cashier and Bradesco? Is the value of the two accounts always the same? If the values differ, what should be done? What is the table name? Which fields are the primary key? Are there other related tables? If there are more than two banks for the same id, how do I relate the records?

  • 1

    Do you know how many banks will have? Or will it be variable?

  • Is there a column that indicates the order of the rows, for the same id value? Or does it come either "box | Bradesco" or "Bradesco | box"?

Show 2 more comments

2 answers

3

I need to do a query that brings values of 2 lines in one

Assuming there is a column seq indicating the order of the lines, by value of id, and that id uniquely identify each pair, 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 there is no column seq and both make the order in which the names of the banks are listed, here is 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 above two codes nome_tabela by the name of the table containing the data.

I’ll improve my question, (...)
The RECPAG field defines the paying bank = p and the receiving bank = 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;

0

I’ll improve my question,

I need to make a query in SQL server EX:

NUM | VALUE | DATE | RECPAG| BANK | TYPE

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 paying bank = p and the receiving bank = R,

I need to unify in an EX line:

NUM | VALUE | DATE | BANK P| BANK R | TYPE

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

00002| 100,00 | 04052018 | BRASIL | CAIXA | TR

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.