Transpose rows to columns

Database
Enthusiast

Re: Transpose rows to columns

Hi Raja,

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 :)

Enthusiast

Re: Transpose rows to columns

Hi,

Can anyone help me in this scenario please.

Junior Contributor

Re: Transpose rows to columns

Hi Sarah,

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');

SELECT
tab.id,
tab.name,
s.subject,
CASE s.subject
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

 Dieter

Enthusiast

Re: Transpose rows to columns

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?

Junior Contributor

Re: Transpose rows to columns

Hi Sarah,

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.

Dieter

Junior Contributor

Re: Transpose rows to columns

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.

Dieter

Enthusiast

Re: Transpose rows to columns

Hi Diether,

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.

Thanks,

Khurram
Junior Contributor

Re: Transpose rows to columns

Hi Khurram,

you'll find all functions for a release in the SQL Functions and Operators manual.

Dieter

Enthusiast

Re: Transpose rows to columns

Thanks for the kind help Dieter :)

Enthusiast

Re: Transpose rows to columns

Dieter,

  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.  

SEL
  INC.INC AS INC_VAL
,IN.DATE_TYPE AS DATE_TYPE_VAL
,SEG.DASHBOARD
,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
   ELSE NULL
END VIS
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'