Example of Oracle row-to-column Conversion

A colleague wrote this article. Let's share it with you.

 

Create Table score
(
Fname nvarchar2 (50 ),
Fsubject nvarchar2 (50 ),
Fresult decimal (18, 0)
);
Insert into score values ('zhang san', 'China', 20 );
Insert into score values ('zhang san', 'mat', 20 );
Insert into score values ('zhang san', 'English ', 20 );
Insert into score values ('zhang san', 'geolocation ', 20 );

Insert into score values ('zhang san', 'China', 1 );
Insert into score values ('zhang san', 'mat', 2 );
Insert into score values ('zhang san', 'English ', 3 );
Insert into score values ('zhang san', 'geolocation ', 4 );

Insert into score values ('Li si', 'China', 20 );
Insert into score values ('Li si', 'mat', 20 );
Insert into score values ('Li si', 'English ', 20 );
Insert into score values ('Li si', 'chemistry ', 20 );
Insert into score values ('Lee si', 'computer techno', 20 );

Begin
Declare v_ SQL nvarchar2 (8000 );
Begin
Select 'select fname name' into v_ SQL from dual;

Select v_ SQL | ',' | ltrim (max (sys_connect_by_path (x, ','), ',') into v_ SQL
From (
Select * from (
Select RN ,'
Sum (Case to_char (fsubject) When ''' | fsubject | '''then fresult else 0 end) '| fsubject x
From (select rownum RN, fsubject from (select fsubject from score group by fsubject) I

) Order by Rn
) Y
Start with Rn = (select count (1) from (select fsubject from score group by fsubject ))
Connect by prior RN-1 = rn;

Select v_ SQL | 'from score group by fname' into v_ SQL from dual;
Dbms_output.put_line (v_ SQL );
End;
End;