All Forums Analytics
dmbass 1 post Joined 08/11
08 Aug 2011
Transpose Rows to columns with 1....N columns

In the past I have used Recursive and Rank Partition By to convert rows to columns. Now I have a new situation. I have data:
AccT_id Cust_id
10XXXXX1 85314587
10XXXXX1 92458714
5XX0XXX5 2587452
5XX0XXX5 4258702
5XX0XXX5 54782142
5XX0XXX5 67842521
5XX0XXX5 85745872

I need to place the cust_id into a colmun as:
Acct_id Cust_id1 Cust_id2 Cust_id3 Cust_id4 Cust_id5 Cust_id(N)
10XXXXX1 85314587 92458714
5XX0XXX5 2587452 4258702 54782142 67842521 85745872

I have not figured out how to make the leap from a set number of columns to unlimited.

current code start.

WITH RECURSIVE temp_table(acct_id, Cust_id1,...Cust_id(N)) AS
(
SELECT hh.acct_id, cga.cust_id ,
SUM(1) OVER (PARTITION BY hh.acct_id ORDER BY cga.cust_id DESC ROWS UNBOUNDED PRECEDING) AS rnk
FROM idw_reports.hh_exclusive_acct_1 AS hh
INNER JOIN idw_reports.cust_grp_asscn_1 AS cga
ON hh.cust_grp_id = cga.cust_grp_id
WHERE hh.ent_type_cd = 'S'
AND hh.curr_in = 1
AND hh.rcv_in = 1
AND cga.asscn_type_cd = 'PRIMARY'
AND cga.cust_grp_type_cd = 'RELTSHP'
AND cga.curr_in = 1
AND cga.rcv_in = 1
)
SELECT *
FROM temp_table

Any ideas how to have adjustable columns?

Kirubhakaran 1 post Joined 06/11
09 Aug 2011

CREATE MULTISET TABLE STOCKS.TEST ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
acct_id VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC,
cust_id VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX ( acct_id ,cust_id );

--The following SQL generates a SQL which can be used to transpose rows

WITH TEMP ( CNT ) AS
(
SELECT MAX(CNT) FROM (
SELECT COUNT( DISTINCT CUST_ID ) CNT
FROM TEST_DB.TEST
GROUP BY ACCT_ID
) A
)
SELECT SQLTXT FROM
(
SELECT 'SELECT ACCT_ID' (VARCHAR(250)) AS SQLTXT , 1 (INTEGER) AS ORDERNUM FROM TEMP

UNION ALL

SELECT ',MAX ( CASE WHEN RANKID = ' || TRIM(CSUM(1,1)) ||
' THEN CUST_ID ELSE NULL END ) AS CUST_ID' || TRIM(CSUM(1,1)) AS SQLTXT, CSUM(1,1) + 1 AS ORDERNUM

FROM TEST_DB.TEST, TEMP
QUALIFY CSUM(1,1) <= CNT

UNION ALL

SELECT 'FROM ( SELECT ACCT_ID, CUST_ID, RANK() OVER (PARTITION BY ACCT_ID ORDER BY CUST_ID) RANKID
FROM TEST_DB.TEST ) A GROUP BY 1;' AS SQLTXT, 1000 AS ORDERNUM FROM TEMP
) A
ORDER BY ORDERNUM
;

--this is the generated SQL

SELECT ACCT_ID
,MAX ( CASE WHEN RANKID = 1 THEN CUST_ID ELSE NULL END ) AS CUST_ID1
,MAX ( CASE WHEN RANKID = 2 THEN CUST_ID ELSE NULL END ) AS CUST_ID2
,MAX ( CASE WHEN RANKID = 3 THEN CUST_ID ELSE NULL END ) AS CUST_ID3
,MAX ( CASE WHEN RANKID = 4 THEN CUST_ID ELSE NULL END ) AS CUST_ID4
,MAX ( CASE WHEN RANKID = 5 THEN CUST_ID ELSE NULL END ) AS CUST_ID5
FROM ( SELECT ACCT_ID, CUST_ID, RANK() OVER (PARTITION BY ACCT_ID ORDER BY CUST_ID) RANKID
FROM TEST_DB.TEST ) A GROUP BY 1;

Regards,
Kirubha

cwindland 11 posts Joined 11/04
13 Mar 2013

I recently created the following dynamic SQL to generate collect statistics statements.  It basically converts row data into column data via an aggregate function and a derived table.
Here are all three statements.  Feel free to change them for your personal needs.
-- - DYNAMIC MULTICOLUMN STATISTICS
-- - Handles up to a 9 column stat.  Keep in mind only the first 16 bytes are collected prior to version 14.
SELECT
    TRIM
        (
      'COLLECT STATISTICS ON ' || TRIM(C.DatabaseName)|| '.'|| TRIM(C.TableName) ||' COLUMN ( '||
      CASE
          WHEN C.STATISTICS_COLUMN_1 IS NOT NULL
              THEN TRIM(C.STATISTICS_COLUMN_1)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_2 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_2)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_3 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_3)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_4 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_4)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_5 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_5)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_6 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_6)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_7 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_7)
          ELSE ' '
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_8 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_8)
          ELSE ' '          
      END ||
      CASE
          WHEN C.STATISTICS_COLUMN_9 IS NOT NULL
              THEN ', '||TRIM(C.STATISTICS_COLUMN_9)
          ELSE ' '
      END
        )|| ' );' AS "DYNAMIC_STAT"
FROM
 (
  SELECT
   TRIM(DatabaseName) AS DatabaseName,
   TRIM(TableName)    AS TableName,
   StatisticsID,
   MAX
   (
    CASE
     WHEN ColumnPosition = 1
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_1,
   MAX
   (
    CASE
     WHEN ColumnPosition = 2
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_2,
   MAX
   (
    CASE
     WHEN ColumnPosition = 3
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_3,
   MAX
   (
    CASE
     WHEN ColumnPosition = 4
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_4,
   MAX
   (
    CASE
     WHEN ColumnPosition = 5
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_5,
   MAX
   (
    CASE
     WHEN ColumnPosition = 6
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_6,
   MAX
   (
    CASE
     WHEN ColumnPosition = 7
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_7,
   MAX
   (
    CASE
     WHEN ColumnPosition = 8
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_8,
   MAX
   (
    CASE
     WHEN ColumnPosition = 9
      THEN TRIM(ColumnName)
    END
   ) AS STATISTICS_COLUMN_9
  FROM
   "DBC".MultiColumnStats
  WHERE DatabaseName = <DatabaseName>
  AND TableName = <TableName>
  
  GROUP BY
   1,
   2,
   3
 ) C
ORDER BY
 1
-- - DYNAMIC MULTICOLUMN INDEX STATISTICS
-- - Handles up to a 35 column index
SELECT
 TRIM
  (
   'COLLECT STATISTICS ON ' || TRIM(C.DatabaseName)|| '.'|| TRIM(C.TableName) ||' COLUMN ( '||
   CASE
    WHEN C.ColumnName_1 IS NOT NULL
     THEN TRIM(C.ColumnName_1)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_2 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_2)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_3 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_3)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_4 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_4)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_5 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_5)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_6 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_6)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_7 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_7)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_8 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_8)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_9 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_9)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_10 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_10)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_11 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_11)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_12 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_12)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_13 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_13)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_14 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_14)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_15 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_15)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_16 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_16)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_17 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_17)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_18 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_18)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_19 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_19)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_20 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_20)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_21 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_21)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_22 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_22)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_23 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_23)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_24 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_24)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_25 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_25)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_26 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_26)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_27 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_27)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_28 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_28)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_29 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_29)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_30 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_30)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_31 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_31)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_32 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_32)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_33 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_33)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_34 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_34)
    ELSE ' '
   END ||
   CASE
    WHEN C.ColumnName_35 IS NOT NULL
     THEN ', '||TRIM(C.ColumnName_35)
    ELSE ' '
   END
  )|| ' );' AS "DYNAMIC_STAT"
FROM
 (
  SELECT
   TRIM(DatabaseName) AS DatabaseName,
   TRIM(TableName)    AS TableName,
   IndexNumber,
   MAX
   (
    CASE
     WHEN ColumnPosition = 1
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_1,
   MAX
   (
    CASE
     WHEN ColumnPosition = 2
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_2,
   MAX
   (
    CASE
     WHEN ColumnPosition = 3
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_3,
   MAX
   (
    CASE
     WHEN ColumnPosition = 4
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_4,
   MAX
   (
    CASE
     WHEN ColumnPosition = 5
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_5,
   MAX
   (
    CASE
     WHEN ColumnPosition = 6
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_6,
   MAX
   (
    CASE
     WHEN ColumnPosition = 7
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_7,
   MAX
   (
    CASE
     WHEN ColumnPosition = 8
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_8,
   MAX
   (
    CASE
     WHEN ColumnPosition = 9
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_9,
   MAX
   (
    CASE
     WHEN ColumnPosition = 10
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_10,
   MAX
   (
    CASE
     WHEN ColumnPosition = 11
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_11,
   MAX
   (
    CASE
     WHEN ColumnPosition = 12
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_12,
   MAX
   (
    CASE
     WHEN ColumnPosition = 13
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_13,
   MAX
   (
    CASE
     WHEN ColumnPosition = 14
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_14,
   MAX
   (
    CASE
     WHEN ColumnPosition = 15
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_15,
   MAX
   (
    CASE
     WHEN ColumnPosition = 16
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_16,
   MAX
   (
    CASE
     WHEN ColumnPosition = 17
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_17,
   MAX
   (
    CASE
     WHEN ColumnPosition = 18
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_18,
   MAX
   (
    CASE
     WHEN ColumnPosition = 19
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_19,
   MAX
   (
    CASE
     WHEN ColumnPosition = 20
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_20,
   MAX
   (
    CASE
     WHEN ColumnPosition = 21
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_21,
   MAX
   (
    CASE
     WHEN ColumnPosition = 22
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_22,
   MAX
   (
    CASE
     WHEN ColumnPosition = 23
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_23,
   MAX
   (
    CASE
     WHEN ColumnPosition = 24
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_24,
   MAX
   (
    CASE
     WHEN ColumnPosition = 25
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_25,
   MAX
   (
    CASE
     WHEN ColumnPosition = 26
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_26,
   MAX
   (
    CASE
     WHEN ColumnPosition = 27
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_27,
   MAX
   (
    CASE
     WHEN ColumnPosition = 28
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_28,
   MAX
   (
    CASE
     WHEN ColumnPosition = 29
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_29,
   MAX
   (
    CASE
     WHEN ColumnPosition = 30
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_30,
   MAX
   (
    CASE
     WHEN ColumnPosition = 31
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_31,
   MAX
   (
    CASE
     WHEN ColumnPosition = 32
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_32,
   MAX
   (
    CASE
     WHEN ColumnPosition = 33
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_33,
   MAX
   (
    CASE
     WHEN ColumnPosition = 34
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_34,
   MAX
   (
    CASE
     WHEN ColumnPosition = 35
      THEN TRIM(ColumnName)
    END
   ) AS ColumnName_35
  FROM
   DBC.IndexStats
  WHERE DatabaseName = <DatabaseName>
  AND TableName = <TableName>
  AND IndexType <> 'J'
  GROUP BY
   1,
   2,
   3
 ) C
ORDER BY
 1
-- - Dynamic Single Column Statistics
 SELECT
 TRIM(
   'COLLECT STATISTICS ON '
   || TRIM(DatabaseName)
   || '.'
   || TRIM(TableName)
   || ' COLUMN ( '
   || ColumnName
  )
   ||' );'
AS "StatisticsStatement"
FROM
 dbc.columnstats
WHERE DatabaseName = <DatabaseName>
AND TableName = <TableName>
ORDER BY ColumnName;
 

GvDa 1 post Joined 07/15
24 Jul 2015

@Kirubha:
Nice solution. Any help on how to execute the resulting SQL?

You must sign in to leave a comment.