none
Trasporre righe in colonne RRS feed

  • Domanda

  • Buongiorno,

    premetto che non uso SQL server, sono un programmatore di automazioni industriali.

    Per una commessa mi hanno chiesto di creare un database sql per l'archiviazione dei dati importanti dell'impianto.

    Uso Siemens TIA PORTAL come software di sviluppo della programmazione PLC che ha la funzione di poter creare degli archivi di variabili come database SQL. Vedo che il database viene creato e da Microsoft SQL Server Managemnet Studio 18 riesco a leggerlo (o anche con dei server free di lettura database sql tipo SysTool SQL MDF Viewer).

    L'unico problema che ho è che il database mi viene visualizzato così (VarName e Value sono le colonne):

    VarName                  VarValue

    Data_1_stored          20

    Data_2_stored          21

    Data_3_stored          22

    Data_4_stored          23

    Data_5_stored          24

    Data_1_stored          30

    Data_2_stored          31

    Data_3_stored          32

    Data_4_stored          33

    Data_5_stored          34

    Mi chiedono però di visualizzarlo così:

    Data_1_stored         Data_2_stored       Data_3_stored       Data_4_stored       Data_5_stored

    20                           21                        22                         23                         24

    30                           31                        32                         33                         34

    Ho provato con "Pivot" e "case" ma non riesco. L'unica Query che mi da qualcosa è questa:

    USE [Database_SQL]

    GO

    SELECT [Data_1_stored], [Data_2_stored], [Data_3_stored], [Data_4_stored], [Data_5_stored]

      FROM

      (select VarName, VarValue

      from [dbo].[ArchivioVariabili_10]) as SourceTable

      PIVOT

      (

      max(VarValue)

      for VarName IN ([Data_1_stored], [Data_2_stored], [Data_3_stored], [Data_4_stored], [Data_5_stored])

      ) AS PivotTable

    GO

    Però vedo solo la prima riga (anche se ho svariati valori memorizzati):

    Data_1_stored         Data_2_stored       Data_3_stored       Data_4_stored       Data_5_stored

    20                           21                        22                         23                         24

    Qualcuno ha dei suggerimenti?


    Grazie in anticipo

    lunedì 21 ottobre 2019 07:10

Risposte

  • ve le soluzioni proposte nell'articolo qui sotto

    https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/


    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    lunedì 21 ottobre 2019 08:16
    Moderatore
  • Ciao Marco,

    oltre al link che ha postato Edoardo, quando si utilizza l'operatore PIVOT è necessario pensare alla soluzione individuando 3 fasi:

    Step 1: "Grouping"

    Come sono correlate le righe della sorgente (tabella che hai) e della destinazione (tabella che vorresti ottenere) ? Servirà probabilmente un raggruppamento, quale elemento deve essere raggruppato? Nel tuo esempio, manca la colonna su cui effettuare il raggruppamento ed è per questo motivo che trovi una riga sola in output. Ho aggiunto la colonna ObjectID al tuo esempio (vedi codice)

    Step 2: "Spreading"

    Pensiamo ora alle colonne, si desidera avere una colonna per ogni attributo, quindi in questo esempio si dovranno avere 5 colonne (Data_1_stored, Data_2_stored, Data_3_stored, Data_4_stored, Data_5_stored).

    Step 3: "Aggregation"

    Per la presenza del raggruppamento, è necessario utilizzare una funzione di aggregazione ad esempio MAX o MIN (entrambe ignorano i valori NULL). L'elemento da aggregare è la colonna VarValue.

    Ecco come si potrebbe ottenere quello che desideri:

    USE [tempdb];
    GO
    
    DROP TABLE IF EXISTS dbo.TestPIVOT;
    GO
    
    CREATE TABLE dbo.TestPIVOT
    (
      ObjectID INTEGER NOT NULL
      ,VarName VARCHAR(40) NOT NULL
      ,VarValue INTEGER NOT NULL
    );
    
    INSERT INTO dbo.TestPIVOT
    (ObjectID, VarName, VarValue)
    VALUES
    (1, 'Data_1_stored', 20),
    (1, 'Data_2_stored', 21),
    (1, 'Data_3_stored', 22),
    (1, 'Data_4_stored', 23),
    (1, 'Data_5_stored', 24),
    (2, 'Data_1_stored', 30),
    (2, 'Data_2_stored', 31),
    (2, 'Data_3_stored', 32),
    (2, 'Data_4_stored', 33),
    (2, 'Data_5_stored', 34);
    GO
    
    SELECT /*ObjectID,*/ /*Step 1: grouping*/
           Data_1_stored, Data_2_stored, Data_3_stored, Data_4_stored, Data_5_stored
    FROM dbo.TestPIVOT
      PIVOT(
             MAX(VarValue) /*Step 3: aggregation*/ FOR VarName /*Step 2: spread by*/
    
             IN([Data_1_stored], [Data_2_stored], [Data_3_stored], [Data_4_stored], [Data_5_stored])
    	   ) AS P;
    GO

    Gli elementi di raggruppamento sono rappresentati dalla lista delle colonne della tabella sorgente, non specificate nelle altre fasi.

    Ciao!



    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    sabato 26 ottobre 2019 18:19
    Moderatore

Tutte le risposte

  • ve le soluzioni proposte nell'articolo qui sotto

    https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/


    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    lunedì 21 ottobre 2019 08:16
    Moderatore
  • Ciao Marco,

    oltre al link che ha postato Edoardo, quando si utilizza l'operatore PIVOT è necessario pensare alla soluzione individuando 3 fasi:

    Step 1: "Grouping"

    Come sono correlate le righe della sorgente (tabella che hai) e della destinazione (tabella che vorresti ottenere) ? Servirà probabilmente un raggruppamento, quale elemento deve essere raggruppato? Nel tuo esempio, manca la colonna su cui effettuare il raggruppamento ed è per questo motivo che trovi una riga sola in output. Ho aggiunto la colonna ObjectID al tuo esempio (vedi codice)

    Step 2: "Spreading"

    Pensiamo ora alle colonne, si desidera avere una colonna per ogni attributo, quindi in questo esempio si dovranno avere 5 colonne (Data_1_stored, Data_2_stored, Data_3_stored, Data_4_stored, Data_5_stored).

    Step 3: "Aggregation"

    Per la presenza del raggruppamento, è necessario utilizzare una funzione di aggregazione ad esempio MAX o MIN (entrambe ignorano i valori NULL). L'elemento da aggregare è la colonna VarValue.

    Ecco come si potrebbe ottenere quello che desideri:

    USE [tempdb];
    GO
    
    DROP TABLE IF EXISTS dbo.TestPIVOT;
    GO
    
    CREATE TABLE dbo.TestPIVOT
    (
      ObjectID INTEGER NOT NULL
      ,VarName VARCHAR(40) NOT NULL
      ,VarValue INTEGER NOT NULL
    );
    
    INSERT INTO dbo.TestPIVOT
    (ObjectID, VarName, VarValue)
    VALUES
    (1, 'Data_1_stored', 20),
    (1, 'Data_2_stored', 21),
    (1, 'Data_3_stored', 22),
    (1, 'Data_4_stored', 23),
    (1, 'Data_5_stored', 24),
    (2, 'Data_1_stored', 30),
    (2, 'Data_2_stored', 31),
    (2, 'Data_3_stored', 32),
    (2, 'Data_4_stored', 33),
    (2, 'Data_5_stored', 34);
    GO
    
    SELECT /*ObjectID,*/ /*Step 1: grouping*/
           Data_1_stored, Data_2_stored, Data_3_stored, Data_4_stored, Data_5_stored
    FROM dbo.TestPIVOT
      PIVOT(
             MAX(VarValue) /*Step 3: aggregation*/ FOR VarName /*Step 2: spread by*/
    
             IN([Data_1_stored], [Data_2_stored], [Data_3_stored], [Data_4_stored], [Data_5_stored])
    	   ) AS P;
    GO

    Gli elementi di raggruppamento sono rappresentati dalla lista delle colonne della tabella sorgente, non specificate nelle altre fasi.

    Ciao!



    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    sabato 26 ottobre 2019 18:19
    Moderatore