Doubt with Rank Over Partition By Function

General

Doubt with Rank Over Partition By Function

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

1001 A,B
1002 A,B
1003 A,B

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?

3 REPLIES
Junior Contributor

Re: Doubt with Rank Over Partition By Function

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?

Re: Doubt with Rank Over Partition By Function

Below is my actual query that I used to achieve the above output:

SELECT

  SALES_ORD_LINE_ITEM_ID,

     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

FROM

(

   SELECT

      a13.SALES_ORD_LINE_ITEM_ID,

      a13.PRODUCTION_LINE_ITEM_ID,

      a11.PRODUCTION_SYS_NAME,

      ROW_NUMBER()

      OVER (PARTITION BY  a13.SALES_ORD_LINE_ITEM_ID

            ORDER BY a11.PRODUCTION_SYS_NAME) AS rn

   FROM

                OPO_PRODUCTION_SYS    a11

                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,

100023368,

100111032,

100085489,

100113083,

100124937)

) 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.

Enthusiast

Re: Doubt with Rank Over Partition By Function

Hi,

check the below logic. this might be helpful.

WITH RECURSIVE LOOKUP(ID1,ID2,DESCR)

AS (

SEL ID1,ID2,DESCR

FROM SRC_TBL_NAME

WHERE ID2=1

UNION ALL

SEL A.ID1,

A.ID2,

B.DESCR||','||A.DESCR AS DESCR

FROM SRC_TBL_NAME A

JOIN LOOKUP B

ON A.ID1 = B.ID1

AND A.ID2=B.ID2+1

)

SEL ID1,MAX(DESCR)

FROM LOOKUP

GROUP BY 1