Transform rows into a select column - PIVOT - SQL Server

Navigation
3

I have a SQL query that returns the following result:

Inquiry 

      +--------------------------------------------------------------------------+
      |  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 rows depending on the rule that I will explain below is 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

  • Select is filtered by SET (in case 8005).
  • If the record has the field PAI equal to NULL it becomes a new record related to your SON along with the content 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 TYPE_DESCRIPTION (TYPE_DESCRIPTION was added by me because it does not have the original table).

I'm sorry if you do not understand the rules, but in short, I'll link the SONS to the FATHER.

    
asked by anonymous 21.09.2017 / 22:32

1 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 the results are grouped by the columns that will be selected in a% w EXCEPT by the column that will be used as VALUE and by the column that will contain the subdivisions. Also note that you want 3 records that have 40, 41, and 42 code in common in the subquery column. If that column is not filled, the values that are considered important for the grouping (in addition to pai ) are those in the conjunto column. Therefore you only need to use the filho function to select the information that is important for the 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 set the column name ISNULL to ordem to match the result you specified as desired in the question. The tipo_conteudo column was not used in tipo just to not divide the results more than desired.

You can check the working result in SQL Fiddle a>.

  

subquery

     

Replaces ISNULL with the specified replacement value.

  

NULL

     

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

  

Using SELECT and PIVOT

     

You can use the relational operators UNPIVOT and PIVOT to change a table-valued expression in another table. UNPIVOT rotates a table-valued expression by transforming the unique values of a column in the expression into multiple columns in the output, as well as executing aggregations where necessary in any remaining column values that are desired in the final output. PIVOT executes the operation opposite to UNPIVOT , turning columns of an expression with table value into column values.

  

PIVOT

     

Returns the maximum value in the expression.

    
21.09.2017 / 23:13
how to work with SASS / LESS in real time? How to get client IP in PHP? [duplicate]