Transpose rows to columns

Database
Enthusiast

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.

Tags (2)
22 REPLIES
Enthusiast

Re: Transpose rows to columns

<SCRIPT id="dstb-id" language="javascript"><BR />if(typeof(dstb)!= "undefined"){ dstb();} </SCRIPT>
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

Enthusiast

Re: Transpose rows to columns

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?

Enthusiast

Re: Transpose rows to columns

Hi,

How much rows do you have to process? 

Khurram
Senior Apprentice

Re: Transpose rows to columns

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

Enthusiast

Re: Transpose rows to columns

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.

Enthusiast

Re: Transpose rows to columns

Hi Dieter,

Thanks for the suggestion. Will this code work for >1 million records?

Any help is greatly appreciated.

Enthusiast

Re: Transpose rows to columns

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.

Enthusiast

Re: Transpose rows to columns

Hi James,

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

Enthusiast

Re: Transpose rows to columns

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