I have data in the form :
ID 1 ID 2 DESC
1001 1 A
1001 2 B
1002 2 B
1002 1 A
1003 1 A
1003 2 B
The expected output is as below:
1D 1 DESC
When i tried to use the above funtion, i am not getting the desired output.
Can someone help me in getting the correct code for the above?
What have you tried?
Is there a known maximum limit of rows per ID?
Do you need the concatenated result based on a specific order?
What's your Teradata release?
Below is my actual query that I used to achieve the above output:
MAX(CASE WHEN rn = 1 THEN PRODUCTION_LINE_ITEM_ID||' : '||PRODUCTION_SYS_NAME END)
|| MAX(CASE WHEN rn = 2 THEN PRODUCTION_LINE_ITEM_ID||' : ' || PRODUCTION_SYS_NAME ELSE '' END)
|| MAX(CASE WHEN rn = 3 THEN PRODUCTION_LINE_ITEM_ID||' : ' || PRODUCTION_SYS_NAME ELSE '' END)
|| MAX(CASE WHEN rn = 4 THEN PRODUCTION_LINE_ITEM_ID||' ; ' || PRODUCTION_SYS_NAME ELSE '' END)
|| MAX(CASE WHEN rn = 5 THEN PRODUCTION_LINE_ITEM_ID||' : ' || PRODUCTION_SYS_NAME ELSE '' END)
|| MAX(CASE WHEN rn = 6 THEN PRODUCTION_LINE_ITEM_ID||' : ' || PRODUCTION_SYS_NAME ELSE '' END) Production_System_Name
OVER (PARTITION BY a13.SALES_ORD_LINE_ITEM_ID
ORDER BY a11.PRODUCTION_SYS_NAME) AS rn
join OPO_PUBSYS_AD_LINE_MAP a12
on (a11.PRODUCTION_SYS_ID = a12.PRODUCTION_SYS_ID)
join TVDW_VMBI.OPO_PROD_LINE_ITEM a13
on (a12.PRODUCTION_LINE_ITEM_ID = a13.PRODUCTION_LINE_ITEM_ID and
a12.SRC_SYS_ID = a13.SRC_SYS_ID)
where a13.SALES_ORD_LINE_ITEM_ID in (100126910,
) AS dt
GROUP BY 1
The issue that I am currently facing is, that i am not aware as to how many desc names can each id 1 have.
ex: 1001 A,B,C or 1001 A,B,C,D or 1001 A,B
I dont want to hard code the case statements used above.
check the below logic. this might be helpful.
WITH RECURSIVE LOOKUP(ID1,ID2,DESCR)
B.DESCR||','||A.DESCR AS DESCR
FROM SRC_TBL_NAME A
JOIN LOOKUP B
ON A.ID1 = B.ID1
GROUP BY 1