Am sure that my column limit would be < 50. But its the record count am worried about which am not sure. I need diffrent approches to perform transpose operation to convert rows to columns. I thought about union, stored procedure and the case statement which Dieter explained. Anyother methods would be greatly appreciated :)
definitely don't try an SP, otherwise 50 columns will probably be processed most performant using a cross join.
If this has to be done repeatedly better use a permanent table (avoids one of the CASE statements, too):
CREATE TABLE subjects ( subject VARCHAR(10));
INSERT INTO subjects ('english');
INSERT INTO subjects ('maths');
INSERT INTO subjects ('science');
WHEN 'english' THEN tab.english
WHEN 'maths' THEN tab.maths
WHEN 'science' THEN tab.science
END AS marks
FROM tab CROSS JOIN subjects AS s
Thank you Dieter. Your suggestion helped me :)
I have one doubt. It will lead to product join if I use cross join rite? Won't that be a problem?
of course this will result in a product join, that's what it should do :-)
There are not many cases where a cross join is actually useful or needed, but this is one.
Btw, in TD14.10 there's a new table UDF TD_UNPIVOT to transform rows to columns.
I didn't test it yet, but i assume it's the fastest way.
Can you please suggest some good place where I can learn Teradata functions, UDFs. I am unable to find a source or list of all these functions.
Following the same thought pattern only using a reference table with more than one column .. would a cross join function better than inner joining ? exampl :
Actual reference table join has 20 -13 joins to pull the filters.
Would performance be better having the reference data in individual tables and cross joining them ?
The dmart.table1 will have a working set of data around 2-4 mill rows. Reference table consists of about 200 rows.
INC.INC AS INC_VAL
,IN.DATE_TYPE AS DATE_TYPE_VAL
,COALESCE(CASE WHEN DIRECTION.FIELDVALUE='%' THEN DIRECTION.FIELDRETURN ELSE TCN.DIRECTION END,'') ||':'|| COALESCE(CASE WHEN MODE_ITEM.FIELDVALUE='%' THEN MODE_ITEM.FIELDRETURN ELSE TCN.MODE_ITEM END,'') ||':'|| COALESCE(CASE WHEN TRANSLOADS.FIELDVALUE='%' THEN TRANSLOADS.FIELDRETURN ELSE TCN.TRANSLOADS END,'') ||':'|| COALESCE(CASE WHEN MOVEMENT.FIELDVALUE='%' THEN MOVEMENT.FIELDRETURN ELSE TCN.MOVEMENTEND,'') AS STREAM
,CASE WHEN SEG.STARTFIELD = 'POSITION1' THEN TCN.POSITION1
WHEN SEG.STARTFIELD = 'POSITION2' THEN TCN.POSITION2
WHEN SEG.STARTFIELD = 'POSITION3' THEN TCN.POSITION3
WHEN SEG.STARTFIELD = 'POSITION4' THEN TCN.POSITION4
WHEN SEG.STARTFIELD = 'POSITION5' THEN TCN.POSITION5
WHEN SEG.STARTFIELD = 'POSITION6' THEN TCN.POSITION6
FROM DMART.TABLE1 TCN
INNER JOIN REFMART.SEGMENTS SEG ON
SEG.DASHBOARD = 'DASHBOARDNAME'
AND SEG.DIRECTION = TCN.DIRECTION
AND SEG.MODE_ITEM = TCN.MODE_ITEM
INNER JOIN REFMART.DATES_INC INC ON
SEG.DASHBOARD = INC.FIELD_NM
AND INC.INC = 0
AND INC.DATE_TYPE IN ('WEEK','MONTH')
INNER JOIN REFMART.DASHBOARD_FILTERS DIRECTION
DIRECTION.FIELDCATEGORY = 'DIRECTION'
AND DIRECTION.DASHBOARD = 'DASHBOARD_DEV'
INNER JOIN REFMART.DASHBOARD_FILTERS MODE_ITEM
MODE_ITEM.FIELDCATEGORY = 'MODE_ITEM'
AND MODE_ITEM.DASHBOARD = 'DASHBOARD_DEV'
INNER JOIN REFMART.DASHBOARD_FILTERS TRANSLOADS
TRANSLOADS.FIELDCATEGORY = 'TRANSLOADS'
AND TRANSLOADS.DASHBOARD = 'DASHBOARD_DEV'
INNER JOIN REFMART.DASHBOARD_FILTERS MOVEMENT
MOVEMENT.FIELDCATEGORY = 'MOVEMENT'
AND MOVEMENT.DASHBOARD = 'DASHBOARD_DEV'