Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

How to Use the PIVOT Function

Document Type | Technical Information

Category | Administration

Applicable Product Versions | 6FS07, 7FS02PS

Document Number | TADTI048

 

Overview

The PIVOT function converts rows into columns. Using the PIVOT feature allows you to write code that is more intuitive compared to the complex and non-intuitive DECODE code. However, even when using the PIVOT feature, you need to predefine the columns to be pivoted, so depending on the situation, please choose the appropriate method between PIVOT and DECODE.
Note
Tibero provides both PIVOT and DECODE features starting from version 6.
 
 
 
 

Method

Syntax

SELECT *
FROM ( Query to be pivoted )
PIVOT ( Aggregate_function(aggregate_column) FOR pivot_column IN (pivot_column_value AS alias ... )

 

Examples

--Number of hires by job and month

SELECT *
 FROM (
        SELECT job , TO_CHAR(hiredate, 'FMMM') || '์›”' hire_month
          FROM emp
      )
PIVOT (
        COUNT(*)
        FOR hire_month IN ('1์›”', '2์›”', '3์›”', '4์›”', '5์›”', '6์›”',
                           '7์›”', '8์›”', '9์›”', '10์›”', '11์›”', '12์›”')
      )

 

--Sum of salaries by job and department code (using pivot column alias)

SELECT job
    , d1
    , d2
    , d3
 FROM (
        SELECT job
             , deptno
             , sal
          FROM emp
      )
PIVOT (
        SUM(sal) FOR deptno IN ('10' AS d1, '20' AS d2, '30' AS d3)
      )

 

--DECODE number of hires by job and month โ†’ pivot is much more intuitive.

SELECT job
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '1', 1, 0)) "1์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '2', 1, 0)) "2์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '3', 1, 0)) "3์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '4', 1, 0)) "4์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '5', 1, 0)) "5์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '6', 1, 0)) "6์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '7', 1, 0)) "7์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '8', 1, 0)) "8์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '9', 1, 0)) "9์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '10', 1, 0)) "10์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '11', 1, 0)) "11์›”"
    , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '12', 1, 0)) "12์›”"
 FROM emp
GROUP BY job

 

 

 

 

Related to