What is PROD_LVL_NM?

Can you share the sample input data and the expected output data to help understand the problem more clearly.

You can UNION multiple SELECT statements to transform the results from rows into columns like

Considering PROD_LVL_NM is a unique random value generated against each row...

SELECT KEY, ROW_NUMBER() OVER(ORDER BY KEY) AS PROD_LVL_NM, PROD_CODE FROM ( SELECT KEY, OA_PRODUCT_1 AS PROD_CODE FROM TBL UNION ALL SELECT KEY, OA_PRODUCT_2 AS PROD_CODE FROM TBL --AND SO ON ) T

Did you try the solution which I suggested earlier? What is missing in it?

Hi,

I don't think there is an efficient way of getting what you want as all the tables joined together (OOFERATTRIBUTE, T_PROD_X_OTHER_HIER_PROD, T_PROD_DEF etc) are dependent on each other.

See if this works for you for two products!

SELECT (200000000000 + B.MAXID + ROW_NUMBER() OVER (ORDER BY SOR_PROD_CD)) AS LEAD_KY ,V.PRODUCT_1 AS SOR_PROD_CD ,COALESCE (LVL.PROD_CAT_LVL_1_NM, '-1') AS PROD_CAT_LVL_1_NM ,CURRENT_TIMESTAMP(0) AS AUD_INSRT_DT_TM ,USER AS AUD_INSRT_USR_ID FROM T_CNTCT_HIST C JOIN (SEL PRODUCT_1, OFFERCODE1 FROM OFFERATTRIBUTE) V ON C.VERS_ID = V.OFFERCODE1 JOIN BTNG.T_PROD_X_OTH_HIER_PROD PRD ON V.PRODUCT_1 = PRD.OTH_HIER_CHAR_PROD_CD AND PRD.EXPIR_DT = '9999-12-31' AND OTH_HIER_KY = 14 JOIN BTNG.T_PROD_DEF DEF ON DEF.PROD_KY = PRD.PROD_KY AND DEF.EXPIR_DT = '9999-12-31' AND PRD.EXPIR_DT = '9999-12-31' JOIN BTNG.T_PROD_CAT_LVL_1_DEF LVL ON DEF.PROD_CAT_LVL_1_KY = LVL.PROD_CAT_LVL_1_KY CROSS JOIN (SELECT COALESCE(MAX(LEAD_KY),0) MAXID FROM T_EMAIL_CCLR_PROD) B UNION ALL SELECT (200000000000 + B.MAXID + ROW_NUMBER() OVER (ORDER BY SOR_PROD_CD)) AS LEAD_KY ,V.PRODUCT_2 AS SOR_PROD_CD ,COALESCE (LVL.PROD_CAT_LVL_1_NM, '-1') AS PROD_CAT_LVL_1_NM ,CURRENT_TIMESTAMP(0) AS AUD_INSRT_DT_TM ,USER AS AUD_INSRT_USR_ID FROM T_CNTCT_HIST C JOIN (SEL PRODUCT_2, OFFERCODE2 FROM OFFERATTRIBUTE) V ON C.VERS_ID = V.OFFERCODE2 JOIN BTNG.T_PROD_X_OTH_HIER_PROD PRD ON V.PRODUCT_2 = PRD.OTH_HIER_CHAR_PROD_CD AND PRD.EXPIR_DT = '9999-12-31' AND OTH_HIER_KY = 14 JOIN BTNG.T_PROD_DEF DEF ON DEF.PROD_KY = PRD.PROD_KY AND DEF.EXPIR_DT = '9999-12-31' AND PRD.EXPIR_DT = '9999-12-31' JOIN BTNG.T_PROD_CAT_LVL_1_DEF LVL ON DEF.PROD_CAT_LVL_1_KY = LVL.PROD_CAT_LVL_1_KY CROSS JOIN (SELECT COALESCE(MAX(LEAD_KY),0) MAXID FROM T_EMAIL_CCLR_PROD) B

There could be some other way but without having some understanding of the data it is difficult to propose any effective solution.

Try this Query, i think it will work...

WITH TEMP (LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_1, PRODUCT_2, PRODUCT_3, PRODUCT_4, PRODUCT_5) as

(SELECT (200000000000 + B.MAXID + ROW_NUMBER() OVER (ORDER BY SOR_PROD_CD)) AS LEAD_KY

,COALESCE (LVL.PROD_CAT_LVL_1_NM, '-1') AS PROD_CAT_LVL_1_NM

, CURRENT_TIMESTAMP(0) AS AUD_INSRT_DT_TM

,USER AS AUD_INSRT_USR_ID

,V.PRODUCT_1 AS SOR_PROD_CD, V.PRODUCT_2, V.PRODUCT_3, V.PRODUCT_4, V.PRODUCT_5

FROM T_CNTCT_HIST C

JOIN

(SEL PRODUCT_1, PRODUCT_2, PRODUCT_3, PRODUCT_4, PRODUCT_5,PRODUCT_6, PRODUCT_7, PRODUCT_8, PRODUCT_9, PRODUCT_10, OFFERCODE1

FROM OFFERATTRIBUTE)V

ON C.VERS_ID = V.OFFERCODE1

JOIN BTNG.T_PROD_X_OTH_HIER_PROD PRD

ON V.PRODUCT_1 = PRD.OTH_HIER_CHAR_PROD_CD AND

PRD.EXPIR_DT = '9999-12-31' AND

OTH_HIER_KY = 14

JOIN BTNG.T_PROD_DEF DEF

ON DEF.PROD_KY = PRD.PROD_KY

AND DEF.EXPIR_DT = '9999-12-31'

AND PRD.EXPIR_DT = '9999-12-31'

JOIN BTNG.T_PROD_CAT_LVL_1_DEF LVL

ON DEF.PROD_CAT_LVL_1_KY = LVL.PROD_CAT_LVL_1_KY

CROSS JOIN

(SELECT COALESCE(MAX(LEAD_KY),0) MAXID FROM T_EMAIL_CCLR_PROD) B)

SEL LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_1 FROM TEMP

UNION ALL

SEL LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_2 FROM TEMP

UNION ALL

SEL LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_3 FROM TEMP

UNION ALL

SEL LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_4 FROM TEMP

UNION ALL

SEL LEAD_KY,PROD_CAT_LVL_1_NM, AUD_INSRT_DT_TM, AUD_INSRT_USR_ID,PRODUCT_5 FROM TEMP

Hi All,

We need to make rows for data in columns. In other works, there are say 5 columns each representing one product and we need to transform that information and create 5 rows one for each product.

Have you ever done that before? Do you have any idea on how we can achieve that?

Example:

Source: OA_PRODUCT_1, OA_PRODUCT_2,OA_PRODUCT_3,OA_PRODUCT_4,OA_PRODUCT_5

Target: KY, PRD_LVL_NM, PROD_CODE

Mapping:for the same key I need to have mulitple PRD_LVL_NM, PROD_CODE each one for different OA_PRODUCT_1, OA_PRODUCT_2,OA_PRODUCT_3,OA_PRODUCT_4,OA_PRODUCT_5.

Result should be something like this:

KY PRD_LVL_NM PROD_CODE

1 XXXX OA_PRODUCT_1

1 YYYY OA_PRODUCT_2

1 ZZZZ OA_PRODUCT_3

1 AAAAA OA_PRODUCT_4

1 BBBBB OA_PRODUCT_5