Tearadata SQL Help - Very much Urgent

Database
Highlighted
Fan

Tearadata SQL Help - Very much Urgent

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

6 REPLIES
Enthusiast

Re: Tearadata SQL Help - Very much Urgent

What is PROD_LVL_NM?

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

Enthusiast

Re: Tearadata SQL Help - Very much Urgent

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
Enthusiast

Re: Tearadata SQL Help - Very much Urgent

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

Enthusiast

Re: Tearadata SQL Help - Very much Urgent

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.

Enthusiast

Re: Tearadata SQL Help - Very much Urgent

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

Fan

Re: Tearadata SQL Help - Very much Urgent

Thanks a lot Aftab and Mathuram --- I will try them out and will let you know if I see any issues --

Thanks once again