I'm new to teradata, and some of the differences really have me stumped. From what I've read I need to replace wm_concat() with a recursive query to aggregate rows, but I'm having a hekuva time figuring it out. Any help you can provide is appreciated.
For the sake of the discussion, I have a table with two columns: 'plan' and 'consolidate'. Each value in 'plan' is unique, and no values in 'consolidate' are believed unique. The 'consolidate' field is used to roll up the 'plan' values into operational groups.
The task is to aggregate the values in the rows of 'plan' into a single row for 'consolidate' seperated by commas or some other delimiter.
So for the following values:
|plan |consolidate|
|MO |1 |
|IL |1 |
|AR |1 |
|KS |2 |
|MS |3 |
|LA |3 |
|TX |3 |
|OK |2 |
I'd like to get the following results (in no particular order):
|consolidate |rollup |
|1 |MO, IL, AR |
|2 |KS, OK |
|3 |MS, LA, TX |
Can anyone help with this? Perhaps a recursive isn't the best solution in Teradata?
Michael Phipps
Technical Business Analyst
Mercy Health Plans
For the sake of the discussion, I have a table with two columns: 'plan' and 'consolidate'. Each value in 'plan' is unique, and no values in 'consolidate' are believed unique. The 'consolidate' field is used to roll up the 'plan' values into operational groups.
The task is to aggregate the values in the rows of 'plan' into a single row for 'consolidate' seperated by commas or some other delimiter.
So for the following values:
|plan |consolidate|
|MO |1 |
|IL |1 |
|AR |1 |
|KS |2 |
|MS |3 |
|LA |3 |
|TX |3 |
|OK |2 |
I'd like to get the following results (in no particular order):
|consolidate |rollup |
|1 |MO, IL, AR |
|2 |KS, OK |
|3 |MS, LA, TX |
Can anyone help with this? Perhaps a recursive isn't the best solution in Teradata?
Michael Phipps
Technical Business Analyst
Mercy Health Plans