Newsgroup: it.comp.appl.access
Titolo: StoredProcedure a campi incrociati

Ciao a tutti,
se fossi stato un novizio questo messaggio si sarebbe intitolato "aiuto!!
Urgentissimo!"
L'argomento e' stato gia' affrontato e ho pure avuto risposta, ma proprio e'
evidente che un mio limite mi impedisce di entrare nella logica.
Facciamo conto che io voglia, da codice, creare una stored procedure a campi
incrociati, qualcuno ne ha una che utilizza di cui mostrarmi il codice sql
per prenderla come spunto? Puo' darsi che vedendone una (o spero piu')
funzionante io riesca a capire come fare.
Grazie
Sib
--
http://www.sitocomune.com

"Sib" <si...@SPAMiol.it> ha scritto nel messaggio
news:m4fSa.323$t92....@news1.tin.it...

Ciao Sib
Se posti un esempio dei dati a disposizione, del risultato che vuoi ottenere
e se la quey  o Sp deve essere dinamica (come quelle di Access) forse posso
darti una mano.
Ciao Giorgio.

 darti una mano.
Presto fatto:
--------
TRANSFORM Sum(ContTess.ConteggioDitessera_n) AS [Il valore]
SELECT ContTess.sez_prov, ContTess.gruppo, ContTess.sez_com,
Sum(ContTess.ConteggioDitessera_n) AS Totale
FROM ContTess
GROUP BY ContTess.sez_prov, ContTess.gruppo, ContTess.sez_com
PIVOT ContTess.anno;
----------------
e anche:
--------
TRANSFORM First(Quote.Quota) AS [Il valore]
SELECT Tessere.anno, Tessere.sez_prov, Tessere.gruppo, Quote.IDTipoTessera
FROM (TipoTessere INNER JOIN Quote ON TipoTessere.IDTipo =
Quote.IDTipoTessera) INNER JOIN Tessere ON TipoTessere.IDTipo =
Tessere.IDTipo
GROUP BY Tessere.anno, Tessere.sez_prov, Tessere.gruppo, Quote.IDTipoTessera
PIVOT Quote.DescQuota;
---------------
Il problema risiede proprio nel fatto che non so' mai a priori quante
colonne vengono fuori.
Sib

 funzionante io riesca a capire come fare.
Continuando con i miei esperimenti, sono riuscito a ottenere un risultato
accettabile, mi resta il problema di come raggruppare in base a piu' campi.
Sib

"Sib" <si...@SPAMiol.it> ha scritto nel messaggio
news:RjsSa.2051$SN1....@news2.tin.it...

[CUT]
Cia Sib
Quindi si tratta di una Query a campi incrociati dinamica ovvero non sai
quanti anni possono essere totalizzati.
A questo proposito utilizziamo la bellissima Sp tratta da ....
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
Necessita di due piccole modifiche, la prima è racchiudere l'output della
somma tra parentesi quadre altrimenti un alias 2000 viene interpretato come
errore e la seconda è l'inserimento del totale.
Creo la Stored Procedure
--------------------------------------------
CREATE PROCEDURE usp_PivotSum
   @table       AS sysname,        -- Table to crosstab
   @onrows      AS nvarchar(128),  -- Grouping key values (on rows)
   @onrowsalias AS sysname = NULL, -- Alias for grouping column
   @oncols      AS nvarchar(128),  -- Destination columns (on columns)
   @sumcol      AS sysname = NULL  -- Data cells
AS
SET NOCOUNT ON
SET SET ANSI_WARNINGS OFF
DECLARE @sql AS varchar(8000), @NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)
SET @sql = 'SELECT' + @NEWLINE + '  ' + @onrows +
CASE
 WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
END
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table
EXEC (@keyssql)
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
 SET @sql = @sql + ',' + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + @sumcol +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key +']'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END
--Riga originale
--SET @sql = @sql         + @NEWLINE +
--Riga modificata per totale
SET @sql = @sql +','+'SUM('+@sumcol+') AS Totale ' + @NEWLINE +
'FROM ' + @table      + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
-- La print serve per vedere il codice generato
--PRINT @Sql
EXEC (@sql)
------------------------------------
Esecuzione della Sp, a fianco ai parametri ti riporto le definizione delle
pivon in access
----------------------------------
Exec usp_PivotSum
      'ContTess',  -- Tabella
     'Sez_Prov,Sez_Com,Gruppo',      -- Campi intestazione riga
      NULL,      -- Alias intestazione colonna
     'Anno',      -- Intestazione colonna
     'ConteggioDiTessera_n'       -- Valore
----------------------------
Spero che i ritorni a capo non facciano confusione, ho provato il tutto
creando una tabella con i nomi dei campi da te postati quindi ti dovrebbe
funzionare al primo colpo.
Ciao Giorgio


In alternativa mi potrei accontentare di un metodo per poter utilizzare il
risultato della sp in una vista o in un'altra sp.
Sib

 funzionante io riesca a capire come fare.
Mi sorge un dubbio:
che alla MS abbiano anche lontanamente immaginato che le maschere "tabella
pivot" siano una soluzione minimamente accettabile del problema?
Sib

 funzionare al primo colpo.
Ti ringrazio molto, si avvicina molto a cio che mi serve.
Scusa se aggiungo tre domande:
Come aggiungi un campo alle intestazioni di riga ?(in realta' mi sono gia'
risposto da solo perche' ho aggiunto qualche riga di codice alla sp per
gestire un nuovo parametro @onrow2)
Come si puo' utilizzare questa sp in una ulteriore sp?
Come affronti in linea di principio questo problema sui tuoi database?
(Scusa ma sono alla fusione, dovrei consegnare qualcosa che faccia finta di
funzionare per martedi', non ce la faro' mai e litighero' col cliente :-( )
Sib

"Sib" <si...@SPAMiol.it> ha scritto nel messaggio
news:CTuSa.2753$SN1.1...@news2.tin.it...

Ciao Sib
Vediamo se ho capito
Se a te interessa avere il resultset della sp come se fosse una tabella o
una vista in modo da inserirla in altre select possiamo utilizzare le
tabelle temporanee quindi nei parametri della sp aggiungiamo la tabella di
output
-----------------
ALTER  PROCEDURE usp_PivotSum
   @table       AS sysname,         -- Table to crosstab
   @onrows      AS nvarchar(128),   -- Grouping key values (on rows)
   @onrowsalias AS sysname = NULL,  -- Alias for grouping column
   @oncols      AS nvarchar(128),   -- Destination columns (on columns)
   @sumcol      AS sysname = NULL,  -- Data cells
   @TbTmp       AS sysname          -- Tabella temporanea di output
AS
ecc.. ecc..
....
....
SET @sql = @sql +','+'SUM('+@sumcol+') AS Totale ' + @NEWLINE +
-- e inseriamo INTO
'INTO ' + @TbTmp      + @NEWLINE +
'FROM ' + @table      + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
--------------
La tua sp chiamante diventa
---------------------------
Exec usp_ContTess
     -- Tabella
     'ContTess',
     -- Campi intestazione riga
     'Sez_Prov,Sez_Com,Gruppo',
     -- Alias intestazione colonna
      NULL,
     -- Intestazione colonna
     'Anno',
     -- Valore
     'ConteggioDiTessera_n',
    -- Tabella temporanea globale con due ##
     '##TempTb'
-- Inserisco i dati in teballa temporanea privata con un #
Select * Into #MiaTmp FROM ##TempTb
-- Elimino tabella temporanea pubblica per renderla libera a altri terminali
DROP TABLE ##TempTb
-- a questo punto ho una tabella privata temporanea da utilizzare in JOIN
con altre tabelle
Select * FROM #MiaTmp
--------------------------------
In linea di principio non uso pivot dinamiche e delimito sempre un delta di
valori (ad esempio 5 o 10 anni) .
Cosě facendo puoi creare una funzione che rende un recordset e che puoi
utilizzare come origine record in JOIN con altre tabelle e intestare le
lable dei report in base al valore @DaAnno
Creazione funzione pivot con delta di 5 anni
------------------------------
CREATE function ufn_PivotSum(@DaAnno int)
RETURNS TABLE AS
RETURN (
SELECT TOP 100 PERCENT
  Sez_Prov,Sez_Com,Gruppo,
  SUM(CASE Anno
        WHEN @DaAnno THEN ConteggioDiTessera_n   ELSE 0
      END) AS Anno0,
  SUM(CASE Anno
        WHEN @DaAnno+1 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno1,
  SUM(CASE Anno
        WHEN @DaAnno+2 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno2,
  SUM(CASE Anno
        WHEN @DaAnno+3 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno3,
  SUM(CASE Anno
        WHEN @DaAnno+4 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno4,
SUM(ConteggioDiTessera_n) AS Totale
FROM ContTess
GROUP BY Sez_Prov,Sez_Com,Gruppo
ORDER BY Sez_Prov,Sez_Com,Gruppo
)
--------------------------------------
Quindi puoi utilizzare il recordset in una sp e metterla in JOIN con altre
tabelle
Select * FROM dbo.ufn_PivotSum(1999) as Pivot
-- Eventule JOIN con altre tabelle
--------------------------------------
Oppure puoi fare il tutto nella stessa sp
Creazione sp pivot con delta di 5 anni
---------------------------------------
CREATE PROCEDURE usp_PivotSum2 (@DaAnno int) AS
SELECT
  Sez_Prov,Sez_Com,Gruppo,
  SUM(CASE Anno
        WHEN @DaAnno THEN ConteggioDiTessera_n   ELSE 0
      END) AS Anno0,
  SUM(CASE Anno
        WHEN @DaAnno+1 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno1,
  SUM(CASE Anno
        WHEN @DaAnno+2 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno2,
  SUM(CASE Anno
        WHEN @DaAnno+3 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno3,
  SUM(CASE Anno
        WHEN @DaAnno+4 THEN ConteggioDiTessera_n ELSE 0
      END) AS Anno4,
SUM(ConteggioDiTessera_n) AS Totale
INTO #Pivot
FROM ContTess
GROUP BY Sez_Prov,Sez_Com,Gruppo
ORDER BY Sez_Prov,Sez_Com,Gruppo
SELECT * FROM #Pivot  -- eventiale JOIN con altre tabelle
------------------------------------------------
Insomma ci possono essere molte strade per risolvere il problema
Ciao Giorgio

 Insomma ci possono essere molte strade per risolvere il problema
Giorgio, ti ringrazio molto per i tuoi validissimi suggerimenti.
Mi studiero' il tuo post con una certa attenzione.
Certamente sono nella situazione di dover cambiare logica (anche per
l'ottimizzazione della strategia client/Server).
Purtroppo la fretta porta sempre a cercare il primo accrocchio che funziona
per poter soddisfare il cliente che ha premura.
Cerchero' di sfruttare i tuoi suggerimenti in due fasi, una immediata per
trovare appunto la prima cosa che funziona, una subito dopo per iniziare
l'ottimizzazione.
Ciao
Sib