Concatenate Vertically?

Analytics
Enthusiast

Re: Concatenate Vertically?

BS,

Try this

SELECT
MAX(CASE WHEN CNT =1 THEN val ELSE ' ' END ) ||
MAX(CASE WHEN CNT =2 THEN ',' || val ELSE ' ' END ) ||
MAX(CASE WHEN CNT =3 THEN ',' || val ELSE ' ' END ) ||
MAX(CASE WHEN CNT =4 THEN ',' || val ELSE ' ' END )
FROM
(
SELECT
val
,ROW_NUMBER() OVER(ORDER BY val) AS CNT
FROM SHARED.TEST
GROUP BY 1) t;

Values considered

sno val
1 USA
2 NYK
3 HKG
4 HKG

Result HKG,NYK,USA

Thanks,
Vinay Bagare

Not applicable

Re: Concatenate Vertically?

hi Gang,

I have a standard hierarchy table with the columns Parent Product Id, Child Product Id and level Number. All of the ulitimate parents do not have parents (Nulls). I need to show the entire lineage on one line (Parent Id, Child Id, Grand Child Id, Great Grandchild Id). The hierarchy only goes four levels deep. Each of the Ids reference a common product set. I've tried CASE, but I get '?'s in my Answer Set in Teradata. Any ideas?
Enthusiast

Re: Concatenate Vertically?

For a fixed length hierarchy, it should be a simple matter of joining the table to itself n - 1 times where n is the number of levels.