All Forums Database
Nani27 3 posts Joined 10/12
18 Oct 2012
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

Qaisar Kiani 337 posts Joined 11/05
18 Oct 2012

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

Qaisar Kiani 337 posts Joined 11/05
18 Oct 2012

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

 

Qaisar Kiani 337 posts Joined 11/05
19 Oct 2012

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

Qaisar Kiani 337 posts Joined 11/05
19 Oct 2012

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.
 

Mathuram 21 posts Joined 09/12
19 Oct 2012

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

Nani27 3 posts Joined 10/12
19 Oct 2012

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

You must sign in to leave a comment.