database – Transform rows into column of a select – PIVOT – SQL Server

Question:

I have an SQL query that returns the following result:

Query

      +--------------------------------------------------------------------------+
      |  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           |
      +--------------------------------------------------------------------------+

However, I want some lines depending on the rule that I will explain below to be returned as a column.

Result

  +-------------------------------------------------------------------------------+  
  | 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

  • The select is filtered by the SET (in case 8005).
  • If the record has the field PARENT equal to NULL, it will be a new record related to its SON along with the content of TIPO_CONTEUDO (ORDER).
  • If the record has a PAI field other than NULL, it must be linked to the respective PAI.
  • The TIPO column is the ID of TIPO_DESCRICAO (TIPO_DESCRICAO was added by me because it doesn't have it in the original table).

Sorry if you don't understand the rules, but in short, I will link CHILDREN SET to the FATHER.

Answer:

Note that the grouping you want to do is performed by 3 columns: the conjunto column, pai and filho . In the case of PIVOT the results are grouped by the columns that will be selected in a subquery EXCEPT for the column that will be used as VALUE and the column that will contain the subdivisions. Also note that you want 3 records that have codes 40, 41 and 42 in common that are in the pai column. If that column is not filled in, the values ​​that are considered important for the grouping (in addition to the conjunto ) are those of the filho column. So you just need to use the ISNULL function 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;

Also note that I adjusted the column name ordem to tipo_conteudo to match the result you specified as desired in the question. The tipo column was not used in the subquery just to not divide the results more than desired.

You can see the result working in SQL Fiddle .

ISNULL

Replaces NULL with the specified replacement value.

GROUP BY

Groups a set of selected rows into a summary rowset by the values ​​of one or more columns or expressions in SQL Server 2014 . One row is returned for each group. Aggregate functions in the SELECT clause's <seleção> list provide information about each group rather than individual rows.

Using PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression in another table. PIVOT rotates a table-valued expression by transforming the unique values ​​of a column in the expression to multiple columns in the output, and performing aggregations where necessary on any remaining column values ​​that are desired in the final output. UNPIVOT performs the opposite operation of PIVOT , rotating columns of a table-valued expression into column values.

MAX

Returns the maximum value in the expression.

Post Views: 0
Scroll to Top