Tunpivot -- without using unpivot component

[Anonymous]

Hi team,

I am using licensed  version 7.2 , I tried to download unpivot component and upload from exchange , but my tool is not supporting  then I tried to do below method

source(db)-->transformer -->tnormalise-->trgtDb

in tmpa - --row6.Apple+";"+row6.Banana+";"+row6.Kiwi

input

date	cntry	Apple	kiwi	orange
12/11/2019	inida	10	20	11
12/12/2019	uk	110	30	44
12/13/2019	us	210	40	33
12/14/2019	malaysia	10	60	66

output

Date ,              country , Derived value , profit

10-10-2019 ,  india   ,   apple, 10

10-10-2019   india   ,    banana,20

10-10-2019   india   ,   kiwi ,30

10-11-2019,uk,Apple,20

10-11-2019,uk,banana,30

10-11-2019,uk,kiwi,40

ana

in derived column I need column name like banana , kiwi

I

[Anonymous]

Hello,

Could you please elaborate your case with an example with expected output values?

Best regards

Sabrina

[manodwhb · Accepted solution]

@Moe , you can create something in the below expression in tMap or tJavarow and write it to a file. so will get the required output.

row1.date+","+row1.cntry+","+"apple"+row.Apple+"\n"+

row1.date+","+row1.cntry+","+"kiwi"+row.kiwi+"\n"+

row1.date+","+row1.cntry+","+"orange"+row.orange

[Anonymous]

Hi All,

To do unpivot when we are using enterprise edition there two option .

1. tsplitrow ( when you have 4 to 5 column ) it will work exactly the way Unpivot sql function will work.

2. SQL when you have 280 or 300 column values)

SELECT EDM_KEY,EDM_CREATED_DATE,EDM_LASTMODIFIED_DATE,SRC_FILE_NAME,YEAR_MONTH_DAY,YEAR,MONTH,mm_CUSTOMER_IDENTIFIER,mm_PRODUCT_IDENTIFIER,'MM' AS SOURCE_FLAG ,'Cust_Value' AS DRIVER_OBJECT_PROFIT, Cust_Value AS PROFIT_VALUE FROM Cust WHERE Cust_Value<> 0 UNION ALL

like this write 280 or 300 stamen using excel .. and notepad++. 

you can achieve 

Tmap -- has limitation -- you wont get derived value (column name )

thanks 

Mohini 

[manodwhb]

@Moe , you can do with tMap the same way you need to write a big expression as I gave in above.