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

 

 

Thanks

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
GROUP BY 1;

Kapil

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.