Turn rows into a select column - PIVOT - SQL Server

Question

I have an SQL query that returns the following result:

Consultation

      +--------------------------------------------------------------------------+
      |  CONJUNTO   | TIPO  | FILHO  |  PAI   | TIPO_CONTEUDO   | TIPO_DESCRICAO |
      +-------------|-------|--------|--------|-----------------|----------------+
      |  8005       | 150   |   40   |  NULL  | 1               | ORDEM          |
      |  8005       | 150   |   41   |  NULL  | 2               | ORDEM          |
      |  8005       | 150   |   42   |  NULL  | 3               | ORDEM          |
      |  8005       | 201   |   43   |  40    | 2005            | ANO            |
      |  8005       | 202   |   44   |  40    | 10              | MES            |
      |  8005       | 203   |   45   |  40    | 101010          | NUMERO         |
      |  8005       | 204   |   46   |  40    | ST 1            | SETOR          |
      |  8005       | 205   |   47   |  40    | TESTE 1         | TIPO           |
      |  8005       | 201   |   48   |  41    | 2006            | ANO            |
      |  8005       | 202   |   49   |  41    | 11              | MES            |
      |  8005       | 203   |   50   |  41    | 202020          | NUMERO         |
      |  8005       | 204   |   51   |  41    | ST 2            | SETOR          |
      |  8005       | 205   |   52   |  41    | TESTE 2         | TIPO           |
      |  8005       | 201   |   53   |  42    | 2007            | ANO            |
      |  8005       | 202   |   54   |  42    | 12              | MES            |
      |  8005       | 203   |   55   |  42    | 303030          | NUMERO         |
      |  8005       | 204   |   56   |  42    | ST 3            | SETOR          |
      |  8005       | 205   |   57   |  42    | TESTE 3         | TIPO           |
      +--------------------------------------------------------------------------+

But I want some lines to depend on the rule that I will explain further below to be returned as a column.

Upshot

  +-------------------------------------------------------------------------------+  
  | CONJUNTO    | TIPO_CONTEUDO | ANO    |  MÊS  |  NUMERO  |   SETOR   | TIPO    |
  +-------------|---------------|--------|-------|----------|-----------|---------+    
  | 8005        | 1             | 2005   |  10   |  101010  |   ST 1    | TESTE 1 |
  | 8005        | 2             | 2006   |  11   |  202020  |   ST 2    | TESTE 2 |
  | 8005        | 3             | 2007   |  12   |  303030  |   ST 3    | TESTE 3 |
  +-------------------------------------------------------------------------------+  

Description

• Select is filtered by SET (in case 8005).
• If the record has the PAI field equal to NULL it is a new record related to your SON along with the contents of TIPO_CONTEUDO (ORDER).
• If the record has the PAI field other than NULL it must be linked to the respective PAI.
• The TYPE column is the ID of the TIPO_DESCRICAO (TIPO_DESCRICAO was added by me because in the original table it does not have).

Sorry if you don’t understand the rules, but in short, I will link the SONS SETS to the FATHER.

Answer 1

Note that the grouping you want to do is done by 3 columns: the column conjunto, pai and filho. In the case of PIVOT results are grouped by the columns that will be selected in a subquery EXCEPT by the column to be used as WORTH and by the column that will contain the subdivisions. Note also that you want 3 records that have in common the code 40, 41 and 42 that are in the column pai. If this column is not filled in, the values which are considered important for the grouping (in addition to conjunto) are those of the column filho. So you just need to use the function ISNULL to select the information that is important for grouping as follows:

SELECT y.conjunto,
       y.ordem AS tipo_conteudo,
       y.ano,
       y.mes,
       y.numero,
       y.setor,
       y.tipo
  FROM (SELECT d.conjunto,
               d.tipo_conteudo,
               d.tipo_descricao,
               ISNULL(d.pai, d.filho) AS id
          FROM dados d) x
 PIVOT (MAX(tipo_conteudo) FOR tipo_descricao IN (ordem, ano, mes, numero, setor, tipo)) y;

Note also that I adjusted the column name ordem for tipo_conteudo to match the result you specified as desired in the question. The column tipo was not used in subquery precisely so as not to divide the results more than desired.

You can check the working result on SQL Fiddle.

---

ISNULL

Replaces NULL at the specified replacement value.

---

GROUP BY

Groups a set of selected rows into a set of summary rows by the values of one or more columns or expressions in the SQL Server 2014. A row is returned to each group. The aggregation functions in the list of <seleção> of the clause SELECT provide information about each group instead of individual lines.

---

Using PIVOT and UNPIVOT

You can use relational operators PIVOT and UNPIVOT to change an expression with table value in another table. PIVOT rotates a table value expression by transforming the unique values of a column into multiple columns in the output, and executing aggregations where they are needed at any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT, rotating columns of an expression with table value in column values.

---

MAX

Returns the maximum value in the expression.