Pivoting DB2 Data - 列数和行数不固定

Question

我在这里看到过一些类似的Q/A，但我见过的案例是“季度销售额”的变化，所以使用了DECODE，有4个类别。

在我的情况下，我不知道有多少行或列的枢轴将有。

| Pay | Age | Value | 

|-----|-----|-------| 

| 1 | 1 | 10 | 

| 1 | 2 | 20 | 

| 1 | 3 | 30 | 

| 2 | 1 | 90 | 

| 2 | 2 | 80 | 

| 2 | 3 | 70 | 

和我们想要的结果设置为

PAYGROUP  Millennials  GenX  Boomers 
1    10   20  30    
2    90   80  70 

这将是很容易与PIVOT声明，即

Transform Max(VALUE) AS V 
SELECT PAYGROUP 
FROM table 
GROUP BY PAYGROUP 
PIVOT AGEGROUP; 

但我DB2没有PIVOT功能。

薪酬组和年龄组的数量可能因案例不同而不同，例如，不同案例的数据可能有不同数量的薪酬和年龄分组。

Answer 1

它可以完成，但只能在SQL过程或其他HLL中完成。

您使用了一条SQL语句来找出多少个不同的值然后使用这些值构建动态SQL语句。

如果您将DB2平台和版本添加到问题中，您可能会指出一个示例。

这里有这里找到一个纯粹的SQL程序（我，但应该LUW工作最初是为DB2是IBM）：https://www.itjungle.com/2015/04/21/fhg042115-story01/

SET SCHEMA = WHER_YOU_WANT_IT; 

CREATE PROCEDURE DO_PIVOT 
    (IN FOR_SCHEMA CHARACTER (10) , 
    IN FOR_TABLE CHARACTER (10) , 
    IN PIVOT_COLUMN VARCHAR (250) , 
    IN VALUE_COLUMN VARCHAR (250) , 
    IN AGG_FUNCTION VARCHAR (5) DEFAULT 'SUM' , 
    IN GROUP_COLUMN VARCHAR (250) DEFAULT NULL) 
    LANGUAGE SQL 
    MODIFIES SQL DATA 
    PROGRAM TYPE SUB 
    CONCURRENT ACCESS RESOLUTION DEFAULT 
    DYNAMIC RESULT SETS 1 
    OLD SAVEPOINT LEVEL COMMIT ON RETURN NO 

BEGIN 

    DECLARE SQLCODE INTEGER DEFAULT 0 ; 
    DECLARE SQL_STATEMENT VARCHAR (5000) ; 
    DECLARE PIVOT_VALUE VARCHAR (20) ; 
    DECLARE PAD CHAR (2) DEFAULT ' ' ; 

    DECLARE C1 CURSOR FOR D1 ; 
    DECLARE C2 CURSOR WITH RETURN FOR D2 ; 

    SET SCHEMA = FOR_SCHEMA ; 

    -- Get the list of values available for the pivot column 
    -- Each value will be a column in the return set 
    SET SQL_STATEMENT = 'select distinct ' 
         || PIVOT_COLUMN 
         || ' from ' 
         || FOR_TABLE 
         || ' order by 1' ; 

    PREPARE D1 FROM SQL_STATEMENT ; 
    OPEN C1 ; 

    -- Construct a dynamic select statement for the pivot 
    SET SQL_STATEMENT = 'select ' ; 

    -- If requested, add the Group By Column 
    -- to the select clause 
    IF GROUP_COLUMN IS NOT NULL THEN 
     SET SQL_STATEMENT = SQL_STATEMENT || GROUP_COLUMN ; 
     SET PAD = ', ' ; 
    END IF ; 

    -- For each possible value for the Pivot Column, 
    -- add a case statement to perform the requested 
    -- aggregate function on the Value Column 
    FETCH NEXT FROM C1 INTO PIVOT_VALUE ; 
    WHILE (SQLCODE >= 0 AND SQLCODE <> 100) DO 
     SET SQL_STATEMENT = SQL_STATEMENT 
          || PAD 
          || AGG_FUNCTION 
          || '(CASE WHEN ' 
          || PIVOT_COLUMN 
          || ' = ''' 
          || PIVOT_VALUE 
          || ''' THEN ' 
          || VALUE_COLUMN 
          || ' END) AS ' 
          || PIVOT_VALUE ; 
     SET PAD = ', ' ; 
     FETCH NEXT FROM C1 INTO PIVOT_VALUE ; 
    END WHILE ; 
    CLOSE C1 ; 

    -- Specify the table to select from 
    SET SQL_STATEMENT = SQL_STATEMENT 
         || ' from ' 
         || FOR_TABLE ; 

    -- If requested, add the Group By Column 
    -- to the select clause 
    IF GROUP_COLUMN IS NOT NULL THEN 
     SET SQL_STATEMENT = SQL_STATEMENT 
          || ' group by ' 
          || GROUP_COLUMN 
          || ' order by ' 
          || GROUP_COLUMN; 
    END IF ; 

    PREPARE D2 FROM SQL_STATEMENT ; 
    OPEN C2 ; 

END ; 

LABEL ON ROUTINE DO_PIVOT 
    (CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR()) 
    IS 'Perform a General Purpose Pivot'; 

COMMENT ON PARAMETER ROUTINE DO_PIVOT 
    (CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR()) 
    (FOR_SCHEMA IS 'Schema for Table' , 
    FOR_TABLE IS 'For Table' , 
    PIVOT_COLUMN IS 'Name of Column to be Pivoted' , 
    VALUE_COLUMN IS 'Column to be Aggregated for Pivot' , 
    AGG_FUNCTION IS 'Use Aggregate Function' , 
    GROUP_COLUMN IS 'Group on Column') ; 

我想你也可以使用XML函数来透视数据......但我还没有找到一个很好的例子