Aster - pivot functionality

Aster
mvn
Enthusiast

Aster - pivot functionality

I have table with below data:

----------------
| Id | Name |
----------------  
| 1  | aaa  |
| 1  | bbb  |
| 1  | ccc  |
| 2  | ddd  |
| 2  | eee  |
--------------

I need output as below.  Which Aster SQL-MR or SQL function can be used to the get below output? Example code would be helpful.

------------------------
| Id |      Name      |
-------------------------  
| 1  | aaa,bbb,ccc  |
| 2  | ddd,eee        |

-------------------------

1 REPLY 1
Highlighted
Teradata Employee

Re: Aster - pivot functionality

You can use NPATH for this.

SELECT * FROM NPATH(

    ON table

    PARTITION BY id

    ORDER BY name  /* Pick any other ordering as required as well */

    SYMBOLS(true as A)

    PATTERN('A*')

    MODE(NONOVERLAPPING)

    RESULT(

       first(id of A) as id,

       accumulate(name of A) as name

   )

);

You can get rid of the [ and ] in the results with regexp_replace in the SELECT like

SELECT id, regexp_replace(name, '\\[|\\]', '','g') name 

...

to get to do your desired result. I haven't tested the regular expression, but that's kind of how it works. 

HTH