Add rows with the same ID in additional columns

Question

Add rows with the same ID in additional columns

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have the following table scenario:

+-------------------------------------------------------------+
| ID    | DATA          |       PROFISSAO       |   SEQUENCIA |
+-------| --------------|-----------------------|-------------+
| 1     | 2018-01-01    |       Professor       |   1         |
| 1     | 2018-02-01    |       Ator            |   2         |
| 1     | 2018-03-01    |       Bombeiro        |   3         |
| 2     | 2018-01-01    |       Comerciante     |   1         |
| 2     | 2018-02-01    |       Motorista       |   2         |
| 2     | 2018-03-01    |       Analista        |   3         |
+-------------------------------------------------------------+

But I want to get the following:

+---------------------------------------------------------------------------------------+
|ID |   DATA        |PROFISSAO      |DATA       |PROFISSAO      |DATA       |PROFISSAO  |
+---|---------------|---------------|-----------|---------------|-----------|-----------|
|1  |   2018-01-01  |Professor      |2018-02-01 |Ator           |2018-03-01 |Bombeiro   |
|2  |   2018-01-01  |Comerciante    |2018-02-01 |Motorista      |2018-03-01 |Analista   |
+---------------------------------------------------------------------------------------+

The SEQUENCE column has been enumerated (a rank in this case).

It turns out that the PIVOTs I see transform the values into the columns and do not add up at the front.

It does not need to be a dynamic value because I know how many lines at most I will have, in which case they will be 3.

sql-server pivot

asked by anonymous 22.05.2018 / 22:32

2 answers

2

Use the classic pivot:

-- código #1
SELECT ID,
       max(case when SEQUENCIA = 1 then DATA end) as [DATA 1],
       max(case when SEQUENCIA = 1 then PROFISSAO end) as [PROFISSAO 1],
       max(case when SEQUENCIA = 2 then DATA end) as [DATA 2],
       max(case when SEQUENCIA = 2 then PROFISSAO end) as [PROFISSAO 2],
       max(case when SEQUENCIA = 3 then DATA end) as [DATA 3],
       max(case when SEQUENCIA = 3 then PROFISSAO end) as [PROFISSAO 3]
  from Tabela
  group by ID;

Simple, practical and efficient, because it performs a single reading on the data.

22.05.2018 / 22:52

Slide with thumbnail using owl carrousel 2 How to trigger the scroll using pure javascript?

score 1 · Accepted Answer

You do not need a pivot . You can bind the table with JOIN :

WITH resultado AS (
  -- Aqui vai sua query atual
)
SELECT r1.*,
       r2.*,
       r3.*
  FROM resultado r1
       LEFT JOIN resultado r2 ON r2.id = r1.id
                             AND r2.sequencia = 2
       LEFT JOIN resultado r3 ON r3.id = r1.id
                             AND r3.sequencia = 3
 WHERE r1.sequencia = 1

See working in SQL Fiddle .