Archives of the TeradataForum

Message Posted: Mon, 10 Jul 2006 @ 15:49:54 GMT

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

Saur,

Assuming you don't have a predefined list of codes that match any given account, you'll need a derived table and a number of case statements to handle this requirement. For this to work well, however, there must be some reasonable limit to the number of codes that can be assigned to any given id. The number of case statements needs to be adjusted to accommodate the maximum number of codes. In the event that there are a huge number of codes per id and coding the case statements becomes arduous, you could write SQL from SQL to generate them.

     /* create the test table */
     CREATE TABLE tdata_test
     (id VARCHAR(10)
     ,tp_code CHAR(6))
     PRIMARY INDEX (id)
     ;

     /* add some test data */
     INSERT tdata_test VALUES ('124999720', 'MBLOTM');
     INSERT tdata_test VALUES ('124999720', 'MBLACF');
     INSERT tdata_test VALUES ('124999720', 'MBLPCP');
     INSERT tdata_test VALUES ('124999720', 'MBLSMS');
     INSERT tdata_test VALUES ('124999720', 'MBLDMF');
     INSERT tdata_test VALUES ('124999721', 'MBLOTM');
     INSERT tdata_test VALUES ('124999721', 'MBLACF');
     INSERT tdata_test VALUES ('124999721', 'MBLPCP');
     INSERT tdata_test VALUES ('124999721', 'MBLSMS');
     INSERT tdata_test VALUES ('124999721', 'MBLDMF');
     INSERT tdata_test VALUES ('124999722', 'MBLOTM');
     INSERT tdata_test VALUES ('124999722', 'MBLACF');
     INSERT tdata_test VALUES ('124999722', 'MBLPCP');
     INSERT tdata_test VALUES ('124999722', 'MBLSMS');
     INSERT tdata_test VALUES ('124999722', 'MBLDMF');

     /* check the max number of codes per id */
     SEL MAX(code_count)
     FROM
     (SEL id
     ,   tp_code
     ,   RANK() OVER (partition BY id ORDER BY id, tp_code) code_count
     FROM tdata_test
     GROUP BY 1,2) TEST;

     Maximum(code_count)
     -------------------
                       5

     /* run the select */
     SEL id
     ,   MAX(CASE WHEN grp_num = 1 THEN tp_code ELSE '' END) AS X1
     ,   MAX(CASE WHEN grp_num = 2 THEN tp_code ELSE '' END) AS X2
     ,   MAX(CASE WHEN grp_num = 3 THEN tp_code ELSE '' END) AS X3
     ,   MAX(CASE WHEN grp_num = 4 THEN tp_code ELSE '' END) AS X4
     ,   MAX(CASE WHEN grp_num = 5 THEN tp_code ELSE '' END) AS X5
     ,   MAX(CASE WHEN grp_num = 6 THEN tp_code ELSE '' END) AS X6
     FROM
     (SEL RANK() OVER (partition BY id ORDER BY id, tp_code)  AS grp_num
     ,   id
     ,   tp_code
     FROM tdata_test)  tdata_test
     GROUP BY 1
     ORDER BY 1

     id           X1       X2       X3       X4       X5       X6
     ----------   ------   ------   ------   ------   ------   ------
     124999720    MBLACF   MBLDMF   MBLOTM   MBLPCP   MBLSMS
     124999721    MBLACF   MBLDMF   MBLOTM   MBLPCP   MBLSMS
     124999722    MBLACF   MBLDMF   MBLOTM   MBLPCP   MBLSMS

Hope this helps,

JL

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