All Forums Database
td@developer 4 posts Joined 12/09
24 Jan 2016
Implement logic in a stored procedure

Hi All,


Need some direction to write the below logic in a SP


Assuming we have a Table A having below data:


Id               Value


110               A


110               T


110               B


112               C


112               P


115               Z 


A particular 'Id' does not have a fix number of records as shown above and it may vary.


Output should look like as follows: 


110              A,T,B


112              C,P


115              Z




Kapil Sehdev 2 posts Joined 07/12
24 Jan 2016

There is a built in function available in teradata that can be used to achieve this. Check for Function availability in your TD Version with the below query :
SELECT FunctionName FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';
If the function exists, you can use a below query to achieve the results :
SELECT ID_column,  TRIM(TRAILING ',' FROM (XMLAGG(Value_column || ',' ORDER BY Value_column ) (VARCHAR(10000))))
FROM your_table_name


Adeel Chaudhry 773 posts Joined 04/08
26 Jan 2016

Or you can simply search for Transpose logic. Should be able to prepare 1 SELECT in SP and run it to INSERT in a table.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.