Problem solve Get help with specific problems with your technologies, process and projects.

Similar 'group by', but horizontal in SQL

With this technique you can totalize in columns with a unique SQL instruction.

With this technique you can totalize in columns with a unique SQL instruction.

The sample shows a report of the number of PF and LF files, and other tables, views or indexes in every library, retrieving data from QADBXREF file.

We have the columns:

dbxLib -> Name of the library
CountPF -> Number of PF's in the LIB
CountLF -> Number of LF's in the LIB
CountSQLs -> Number of other SQL's object types
CountTOTAL -> Total Objects in the LIB
TestSum -> a sample for the SUM column function, the value of this column is for learn purpose.

I made this sample to show you the tip with a system file that you have in your system and can test quickly. But the general purpose of the tip is the columns reports such as, sales of a year in quarters.

select dbxLib,          
count(case when dbxAtr='PF' then dbxAtr end) as CountPF, 
count(case when dbxAtr='LF' then dbxAtr end) as CountLF, 
count(case when not(dbxAtr in('LF', 'PF'))               
then dbxAtr end) as CountSQLs,                           
count(dbxAtr) as CountTOTAL,                             
decimal(sum(case when dbxAtr in('PF', 'TB')              
then dbxNFl end), 11, 0) as TestSum                      

from QADBXREF            
group by dbxLib                                  


The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming