transpose rows to columns using With Recursive SQL

Database
Teradata Employee

transpose rows to columns using With Recursive SQL

hi

 

I want to transpose rows into columns using 'With Recursice' SQL. Can you kindly provide SQL to this. 

The table looks like

 

SyntaxEditor Code Snippet

CREATE MULTISET TABLE test.val ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(group_id VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC,Group_value VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC)PRIMARY INDEX ( group_id ,group_value );



INSERT INTO test.val SEL 'A',10;
INSERT INTO test.val SEL 'B',21;
INSERT INTO test.val SEL 'A',30;
INSERT INTO test.val SEL 'A',40;
INSERT INTO test.val SEL 'B',35;
INSERT INTO test.val SEL 'A', 60 ;
INSERT INTO test.val SEL 'B',72;
INSERT INTO test.val SEL 'A',90;

sel * from test.val;

Capture.JPG

I want these values to be represented like snapshot below using recursive SQL

Capture1.JPG

Thanks in adavnce.

 

Tags (2)
1 REPLY 1
Enthusiast

Re: transpose rows to columns using With Recursive SQL

I have tried using xmlagg as something like below, but seems on newer versions we have pivot, unpivot to acheive the transpose of rows and columns

 

create multiset volatile table qq(col1 varchar(2),col2 int) on commit preserve rows;

insert into qq('a',1);
insert into qq('a',2);
insert into qq('a',3);
insert into qq('a',4);
insert into qq('c',666);
insert into qq('c',555);
insert into qq('b',22);
insert into qq('b',33);

select col1,TRIM(TRAILING ',' FROM (XMLAGG(col2 || ','  ORDER BY col2 )(varchar(200)))) from qq group by col1;

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR