Archives of the TeradataForum

Message Posted: Wed, 14 May 2008 @ 19:44:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Uukasz,

As far as I understand it you like to transpose/pivot a number of rows into a one row list per parent_id.

If there is a known max value of rows per parent_id that you can limit your list to, then a clasical method is to use aggregation with max on a number of case-statements, like...

     SELECT parent_id
       ,MAX(CASE WHEN e1.x_order = 0 THEN ''||e1.installed_date ELSE '' END) AS data0
       ,MAX(CASE WHEN e1.x_order = 0 THEN e1.prod_id ELSE '' END) AS prod0
       ,MAX(CASE WHEN e1.x_order = 1 THEN ''||e1.installed_date ELSE '' END) AS data1
       ,MAX(CASE WHEN e1.x_order = 1 THEN e1.prod_id ELSE '' END) AS prod1
       ,MAX(CASE WHEN e1.x_order = 2 THEN ''||e1.installed_date ELSE '' END) AS data2
       ,MAX(CASE WHEN e1.x_order = 2 THEN e1.prod_id ELSE '' END) AS prod2
       ,MAX(CASE WHEN e1.x_order = 3 THEN ''||e1.installed_date ELSE '' END) AS data3
       ,MAX(CASE WHEN e1.x_order = 3 THEN e1.prod_id ELSE '' END) AS prod3
       ,MAX(CASE WHEN e1.x_order = 4 THEN ''||e1.installed_date ELSE '' END) AS data4
       ,MAX(CASE WHEN e1.x_order = 4 THEN e1.prod_id ELSE '' END) AS prod4
       ,MAX(CASE WHEN e1.x_order = 5 THEN ''||e1.installed_date ELSE '' END) AS data5
       ,MAX(CASE WHEN e1.x_order = 5 THEN e1.prod_id ELSE '' END) AS prod5
       /* etc to x_order = known value N */
     FROM (
     SELECT e0.*
       ,( RANK()OVER(PARTITION BY e0.parent_id
          ORDER BY e0.installed_date DESC, e0.prod_id ASC)
         - 1 ) AS x_order
     FROM example AS e0
     ) AS e1
     GROUP BY 1
     ;

If you on the other hand do not have a known max number of data-items in the list and are looking for a "dynamic" length list solotion, then you should try to use a recursive query, like ...

     CREATE VOLATILE TABLE example_0
     AS (
     SELECT e0.*
       ,( RANK()OVER(PARTITION BY e0.parent_id
          ORDER BY e0.installed_date DESC, e0.prod_id ASC)
         - 1 ) AS x_order
     FROM example AS e0
     ) WITH DATA
     ON COMMIT PRESERVE ROWS
     ;

     WITH RECURSIVE build_list
     (parent_id,iter,data_list)
     AS (
     SELECT e0.parent_id
       ,0 (INTEGER) AS x_iter
       ,TRANSLATE((CAST(e0.installed_date AS FORMAT 'YYYYMMDD') || '|' ||
     TRIM(e0.prod_id)
         ) USING UNICODE_TO_LATIN) (VARCHAR(21000)) AS x_list
     FROM example_0 AS e0
     WHERE e0.x_order = (x_iter)
     UNION ALL
     SELECT b0.parent_id
       ,b0.iter+1 (INTEGER) AS x_iter
       ,( TRIM(b0.data_list)
         || TRANSLATE(('|' || CAST(e1.installed_date AS FORMAT 'YYYYMMDD') || '|'
       ||TRIM(e1.prod_id)
         ) USING UNICODE_TO_LATIN)
        ) (VARCHAR(21000)) AS x_list
     FROM build_list as b0
     INNER JOIN example_0 AS e1
     ON e1.parent_id = b0.parent_id
     AND e1.x_order = (x_iter)
     WHERE (x_iter) < 100
     )
     SELECT b1.*
     FROM build_list as b1
     QUALIFY RANK()OVER(PARTITION BY b1.parent_id ORDER BY b1.iter DESC)=1
     ;


     /*returns
     parent_id   iter   data_list
     100           5    20040101|05|20031201|04|20031101|03|20021201|02|20021101|01
     200           3    20031101|03|20021201|02|20021101|01
     */

Jonas Blomqvist

	<Prev	Next>		<<First	<Prev	Next>	Last>>