PIVOT FUNCTION in Teradata 15.10

Database
Enthusiast

PIVOT FUNCTION in Teradata 15.10

Hi All ,

 

I have created the table with records like

 

SyntaxEditor Code Snippet

CREATE TABLE TEST.PIVOT_EX(ENO INTEGER,ENAME VARCHAR(50),SUBJECT VARCHAR(50),MARKS INTEGER)PRIMARY INDEX (ENO);

INSERT INTO TEST.PIVOT_EX VALUES (1,'John','Maths',100);
INSERT INTO TEST.PIVOT_EX VALUES (1,'John','PCM',40);
INSERT INTO TEST.PIVOT_EX VALUES (1,'John','SC',20);

I want to transpose rows to columns .

I can do the same using many other methods ,like one of them mentioned below

SyntaxEditor Code Snippet
SELECT ENO,ENAME,MAX(CASE WHEN SUBJECT='MATHS' THEN MARKS END) MATHS,MAX(CASE WHEN SUBJECT='PCM' THEN MARKS END) PCM,MAX(CASE WHEN SUBJECT='SC' THEN MARKS END) SC
FROM TEST.PIVOT_EX
GROUP BY 1,2;


I want to use PIVOT to achieve the same . I used below synatx but getting error

SyntaxEditor Code Snippet
SELECT * FROM  TEST.PIVOT_EX  TD_SYSFNLIB.PIVOT(MAX(MARKS) AS B1 FOR SUBJECT IN  (MATHS,PCM,SC)) tmp;

Please help me on this
 

Accepted Solutions
Junior Contributor

Re: PIVOT FUNCTION in Teradata 15.10

You're right, the latest version of the 15.10 SQL Functions, Operators, Expressions, and Predicates manual includes both PIVOT and UNPIVOT. This probably slipped in accidentally from the 16 manuals, seems to be a documentation bug.

1 ACCEPTED SOLUTION
10 REPLIES
Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

Hi,

I dont think there is Pivot function in TD. There is a TD_unpivot function. Just do a help database TD_SYSFNLIB and you wont find pivot funtion.

--Samir                  

Teradata Employee

Re: PIVOT FUNCTION in Teradata 15.10

PIVOT will be included in Teradata 16.
Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

I have used xmlagg function for changing rows to columns. You can check if that is something that you can use.

--samir

Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

Hi ,

 

PIVOT and UNPIVOT is included in Teradata 15.10

 

I got a reference from one of the document found in Teradata site.

 

Regards

Abhishek

Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

Unpivot yes, pivot is included starting 16.00.

Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

This means Teradata included the topic in their manuals before the release .

Junior Contributor

Re: PIVOT FUNCTION in Teradata 15.10

No, this means you read a manual not matching the version of your Teradata system :-)

Enthusiast

Re: PIVOT FUNCTION in Teradata 15.10

Actually i am using Teradata 15.10 sandbox and followed the Teradata 15.10 manual only  :)

 

So i am assuming PIVOT  function is working on Teradata 16.0 and not Teradata 15.10 

Junior Contributor

Re: PIVOT FUNCTION in Teradata 15.10

You're right, the latest version of the 15.10 SQL Functions, Operators, Expressions, and Predicates manual includes both PIVOT and UNPIVOT. This probably slipped in accidentally from the 16 manuals, seems to be a documentation bug.