Transpose Rows to columns with 1....N columns

Analytics
N/A

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?

3 REPLIES

Re: Transpose Rows to columns with 1....N columns

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;
Enthusiast

Re: Transpose Rows to columns with 1....N columns

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;

N/A

Re: Transpose Rows to columns with 1....N columns

@Kirubha:

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