All Forums Database
Sarah_07 19 posts Joined 05/13
23 Oct 2013
Transpose rows to columns

Hi All,
I'm new to TD. I need to transpose data from rows to columns. I'm not sure how to do it using PIVOT in teradata. Can anyone help me.
Sample input data.
ID Name English Maths Science
123 ABC   90       95       95
345 CDE   70       80       90
 
My output should look like,
ID Name Subject Marks
123 ABC English  90
123 ABC Maths    95
123 ABC Science 95
345 CDE English 70
345 CDE Maths   80
345 CDE Science 90
Please do help me.
Thanks in advance.
 

Thanks, Sarah
james.rice 6 posts Joined 09/12
23 Oct 2013

if(typeof(dstb)!= "undefined"){ dstb();}
i would think union would suffice .. ?
 
sel
   t.id
  ,t.name
  ,'english' as subject
  ,t.english
from db.tbl t
union
sel
   t.id
  ,t.name
  ,'maths' as subject
  ,t.maths
from db.tbl t
union
sel
   t.id
  ,t.name
  ,'sicience' as subject
  ,t.science
from db.tbl t

Sarah_07 19 posts Joined 05/13
23 Oct 2013

Thanks James, for your timely reply. But the data I have provided is just a sample one. I know this could be done using union. But i have huge number of data, also using union would affect the performance for sure. Is there any alternate way to attain this?

Thanks,
Sarah

M.Saeed Khurram 544 posts Joined 09/12
23 Oct 2013

Hi,
How much rows do you have to process? 
 

Khurram

dnoeth 4628 posts Joined 11/04
23 Oct 2013

Hi Sarah,
instead of UNION (better UNION ALL to avoid DISTINCT processing) you can cross join to a table(query returning one row per column:

CREATE VOLATILE TABLE vt ( i INT) ON COMMIT PRESERVE ROWS;
INSERT INTO vt (1);
INSERT INTO vt (2);
INSERT INTO vt (3);

SELECT 
  id,
  name,
  CASE i 
     WHEN 1 THEN 'english'
     WHEN 2 THEN 'maths'
     WHEN 3 THEN 'science'
  END AS subject, 
  CASE i 
     WHEN 1 THEN english
     WHEN 2 THEN maths
     WHEN 3 THEN science
  END AS marks
FROM tab CROSS JOIN vt

This normally outperforms UNION.
 
Dieter

Dieter

james.rice 6 posts Joined 09/12
23 Oct 2013

My appologies Sarah I should have assumed that this was for large volumes of data.
Dieter love the cross join suggestion and will have to look into using for a process I run that uses a reference table to perform a similar function but uses regular inner join on fieldnm, field value and case statements like your sugestion.  I would be interested to see if the cross join performs faster than implicit inner joins.
 

Sarah_07 19 posts Joined 05/13
23 Oct 2013

Hi Dieter,
Thanks for the suggestion. Will this code work for >1 million records?
Any help is greatly appreciated.

Thanks,
Sarah

james.rice 6 posts Joined 09/12
23 Oct 2013

Sarah,
   I do use a similar process to Dieter's suggestion only i use a perm reference table and hard inner joins against tables with ~200 mil records and yes it works well. In fact i tested his suggestion using the cross join to compare against hard inner joins and it did perform slightly faster. Im guessing the PE didnt have to work as hard to convert it.

Sarah_07 19 posts Joined 05/13
23 Oct 2013

Hi James,
If you dont mind, can you post your code here. So that I'll be able to understand it more clearly.

Thanks,
Sarah

Raja_KT 1246 posts Joined 07/09
23 Oct 2013

Hi,
 
The column limit for Teradata is there. As far as I know it is 2048 columns. Please check. Also check the design :) . There are assorted implications.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Sarah_07 19 posts Joined 05/13
24 Oct 2013

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

Thanks,
Sarah

Sarah_07 19 posts Joined 05/13
24 Oct 2013

Hi,
Can anyone help me in this scenario please.

Thanks,
Sarah

dnoeth 4628 posts Joined 11/04
25 Oct 2013

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

Dieter

Sarah_07 19 posts Joined 05/13
25 Oct 2013

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?

Thanks,
Sarah

dnoeth 4628 posts Joined 11/04
25 Oct 2013

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

Dieter

dnoeth 4628 posts Joined 11/04
26 Oct 2013

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

Dieter

M.Saeed Khurram 544 posts Joined 09/12
26 Oct 2013

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

dnoeth 4628 posts Joined 11/04
26 Oct 2013

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

Dieter

Sarah_07 19 posts Joined 05/13
28 Oct 2013

Thanks for the kind help Dieter :)

Thanks,
Sarah

james.rice 6 posts Joined 09/12
29 Oct 2013

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'
   

     
  
 

gerardo 13 posts Joined 10/09
04 Dec 2013

try,
 
select id, name,   
max(case when subjkect ='Maths' then msk else null end),
 max(case when subjkect ='Science' then msk else null end),
 max(case when subjkect ='english' then msk else null end)
from table
group by 1,2

mlachlan 1 post Joined 05/14
02 Mar 2015

I'm having a Table called Test_Pivot which looks like
ID   VALUE  MONTH
1    5          201501
1    10        201501
1    10        201502
The output should look like this:
ID  201591 201502
----------------------
1    10        10
showing the Maximum of each month.
 
Any Ideas ?
 
Regards
Yorkie

Arparmar 8 posts Joined 02/16
19 Feb 2016

HI Mlachlan,
please try this one.

sel 01 as  id,

( sel max(val)  from tab where mnth=201501 and id=01 group  by id,mnth)   as "201501",

( sel max(val)  from tab where mnth=201502 and id=01 group  by id,mnth)   as "201502",

 ( sel max(val)  from tab where mnth=201503 and id=01 group  by id,mnth)   as "201503"

from tab

group by 1,2,3,4

You must sign in to leave a comment.