Add rows with the same ID in additional columns

Question

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 SEQUENCIA column has been listed (a rank in the case).

It turns out that the Pivots that I see transform the values in the columns and do not add to the front.

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

Answer 1

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, as it performs a single reading in the data.

Answer 2

You don’t need one pivot. You can link 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 on SQL Fiddle.