Tableau croisé SQL Pivot Table

page d'accueil
Boite à outils

Cette page a été mise à jour le 20 janvier 2009
Contact

Welcome page
Tools box

 Versionning :

Vous avez un fichier avec ce genre d'information

You have a file with this kind of statistic data :

 

(dds)
0001.00 R PIVOTF 0002.00 DATE L 0003.00 REGION 20 0004.00 QUANTITY 10S 3
0005.00 AMOUNT 15S 4
(sql)
DATE PIVOTP DATE REGION PIVOTP CHARACTER 20 QUANTITY PIVOTP NUMERIC 10 3
AMOUNT PIVOTP NUMERIC 15 4
par exemple avec ces données :

for example, with these values :


DATE REGION QUANTITY AMOUNT 01/05/05 one 10,000 10,0000 01/05/05 two 20,000 40,0000 01/05/05 three 30,000 90,0000 02/05/05 one 10,000 10,0000 02/05/05 two 20,000 40,0000 02/05/05 three 30,000 90,0000 03/05/05 one 10,000 10,0000 03/05/05 two 20,000 40,0000 03/05/05 three 30,000 90,0000

un tableau coisé montrera ces colonnes : 

  • Date 
  • Quantity--Region 1 
  • Quantity--Region 2 
  • Quantity--Region 3 
  • Quantity--Total Regions 
  • Amount--Region 1 
  • Amount--Region 2 
  • Amount--Region 3 
  • Amount--Total Regions 

La clé de ce rapport est que le nombre de colonnes dépend du nombre de régions

ce sql :

a pivot report will shows colums : 

  • Date Sold 
  • Quantity--Region 1 
  • Quantity--Region 2 
  • Quantity--Region 3 
  • Quantity--Total for All Regions 
  • Amount--Region 1 
  • Amount--Region 2 
  • Amount--Region 3 
  • Amount--Total for All Regions 

The key of the report is that the number of columns is depending on the number of distinct REGION values 

this sql :


select distinct region from pivotp
réponds

answers :


REGION
one two three
ce qui donne exactement ce que l'on recherche. Avec ces valeurs clés cela devient facile de construire cette requête

that are the key we search. With these keys, we can simply construct this string


select char(date, iso) date, sum(quantity_one ) quantity_one ,
sum(quantity_two ) quantity_two ,
sum(quantity_three ) quantity_three ,
sum(quantity_total ) quantity_total ,
sum(amount_one ) amount_one ,
sum(amount_two ) amount_two ,
sum(amount_three ) amount_three ,
sum(amount_total ) amount_total from ( SELECT DATE , case when region = 'one' then quantity end quantity_one , case when region = 'two' then quantity end quantity_two , case when region = 'three' then quantity end quantity_three
, quantity quantity_total
, case when region = 'one' then amount end amount_one , case when region = 'two' then amount end amount_two , case when region = 'three' then amount end amount_three , amount amount_total FROM pivotp ) as pivot group by date
que l'on utilisera dans un sql dynamique RPG (modèle : voir sylk21 dans SYLK2) ou dans un RUNSQLSTL ou un STRQMQRY 

réponse :

that is to be run as dynamic rpg sql (rpg sample is sylk21 at SYLK2) (or with RUNSQLSTM or with STRQMQRY ...)

it answers :

 
DATE QUANTITY_ONE QUANTITY_TWO QUANTITY_THREE QUANTITY_TOTAL AMOUNT_ONE AMOUNT_TWO AMOUNT_THREE AMOUNT_TOTAL
01/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140
02/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140
03/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140
la réponse de SQL400 fait ici plus de 350 caractères de large, ce qui n'est pas très aisé à utiliser. C'est la raison pour laquelle vous avez intérêt à exporter directement au format excel. Je sais, j'aurais pu ajouter des fonctions DECIMAL pour raccourcir un peu, mais je préfère excel.

Il ne manque que le total général. Ce SQL s'en charge

actual sql400 response width is here 350+ characters, that is not easy to read. It's the reason why you have a better choice to export directly in excel format. Yes, I know I could have added some DECIMAL function, but excel is a better choice.

Now the horizontal total is missing. This SQL add the missing line :


select 'total',sum(quantity_one ) quantity_one , sum(quantity_two ) quantity_two , sum(quantity_three ) quantity_three , sum(quantity_total ) quantity_total , sum(amount_one ) amount_one , sum(amount_two ) amount_two , sum(amount_three ) amount_three , sum(amount_total ) amount_total from ( SELECT DATE , case when region = 'one' then quantity end quantity_one , case when region = 'two' then quantity end quantity_two , case when region = 'three' then quantity end quantity_three
, quantity quantity_total
, case when region = 'one' then amount end amount_one , case when region = 'two' then amount end amount_two , case when region = 'three' then amount end amount_three , amount amount_total FROM pivotp ) as pivot

réponse :

it answers


total 30,000 60,000 90,000 180,000 30,0000 120,0000 270,0000 420,0000

Si vous travaillez en RPG, utilisez deux SQL séparés, c'est plus pratique

Si vous travaillez avec STRQMQRY (donc vous devrez compiler le qmqry avant de l'utiliser, par exemple dans QTEMP) pour obtenir le résultat dans un fichier créé à la volée, préférez une union des deux sql ... ou ajoutez un ; (point virgule) entre les deux sql

Si vous travaillez avec RUNSQLSTM (donc pas besoin de recompiler), vous devrez ajouter un CREATE avant le premier SQL ... et incorporer vos SELECT dans des INSERT INTO

If you work within an RPG program, you have a good choice to work with two independant sql select.

If you work with a STRQMQRY (ie you have to compile the qmqry before running it, for example in QTEMP) to output the select to a dynamicaly created file, you can have a better choice to run an union of the two sqls. Or run each select separated by a semicolon

If you work with RUNSQLSTM (ie you don't want to compile an object), you have to add a sql CREATE before the first select ... and embed the SELECTs into INSERT INTO

Procédure de test :

  • adapter et vérifier le code SQL avec STRSQL

  • vérifier l'extraction vers un fichier, toujours avec STRSQL

c'est comme ça que j'ai construit cette page

  • créer le programme RPG qui va ensuite construire le SQL dynamique

  • exécuter ce SQL. De préférence dans le RPG ci-dessus, c'est plus pratique pour la maintenance

Test procedure: 
  • adapt and verify the SQL code with STRSQL 
  • verify the extraction to a file, always with STRSQL 

it is like that that I constructed this page 

  • create the RPG program that is going to construct the dynamic SQL
  • then execute this SQL. Prefer in the above RPG, more convenient for the maintenance
j'ai construit la démonstration comme ceci : I have build this demonstration :

RUNSQLSTM SRCMBR(PIVOTCOUNT) COMMIT(*NONE) SQL2SYLK SQL('select * from pivotcount') IFSDIR(TEMP) IFSFILE('pivotcount.slk') RUNSQLSTM SRCMBR(PIVOTSUM) COMMIT(*NONE) SQL2SYLK SQL('select * from pivotsum')
IFSDIR(TEMP) IFSFILE('pivotsum.slk')
ci-après les fichiers correspondants hereafter the corresponding files

 PIVOTCOUNT.txt

PIVOTSUM.txt

sqlpivotcount.slk

sqlpivotsum.slk

You are looking for a more classic solution ? go to

Creating Pivot Tables on the iSeries
by Bruce Guetzkow
http://www.itjungle.com/fhg/fhg052505-story01.html

The source code is provided as an open source tool under the GNU General Public License, version 2.  In summary, this license specifies:

By clicking the download link you acknowledge and agree to the terms of the license.

Download the (zipped) SAVF containing the JPLTOOLS : the source file, the message file, the bnddir

THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.