pivot and unpivot in sql server

 ---PIVOT EXAMPLE 

Drop table Students;

CREATE TABLE Students

(

Id INT PRIMARY KEY IDENTITY,

StudentName VARCHAR (50),

Course VARCHAR (50),

Score INT

);

INSERT INTO dbo.Students(StudentName,Course,Score) VALUES ('Abhishek', 'English', 95 )

INSERT INTO Students VALUES ('Abhishek', 'History', 82)

INSERT INTO Students VALUES ('Nayan', 'English', 45)

INSERT INTO Students VALUES ('Nayan', 'History', 78);

SELECT * FROM  

(SELECT StudentName,Score,Course FROM Students) AS StudentTable

PIVOT( SUM(Score) FOR Course IN ([English],[History])) AS SchoolPivot 

--UNPIVOT 

Drop table Students;

CREATE TABLE Students

(

Id INT PRIMARY KEY IDENTITY,

StudentName VARCHAR (50),

Math INT,

English INT,

History INT,

Science INT

);

INSERT INTO Students VALUES ('Abhishek', 78, 85, 91, 76 )

INSERT INTO Students VALUES ('Nayan', 87, 90, 82, 87)

SELECT StudentName, Course, Score

FROM Students

UNPIVOT

( Score FOR Course in (Math, English, History, Science)) AS SchoolUnpivot

--now pivot the above 

select * from (

SELECT StudentName, Course, Score

FROM Students

UNPIVOT

( Score FOR Course in (Math, English, History, Science)) AS SchoolUnpivot) AS StudentTable

PIVOT( SUM(Score) FOR Course IN ([English],[History])) AS SchoolPivot 

 

Pipeline function in oracle

 

--Q: what is pipeline function : 

--Q: What is pipe rows 

--Q: why return is empty ?

drop type country_type force 

---1 create the object type 

create or replace type country_type as OBJECT (

country_id char(2), 

country_name VARCHAR2(100)

);

---2 create the object of that type (step1) 

create or replace type country_table_type as TABLE OF country_type;

---3 create the pipeline function 

create or replace function fun_get_country_details return country_table_type 

PIPELINED  

AS

BEGIN

  FOR i_rec IN (select * from countries) LOOP

    PIPE ROW(country_type(i_rec.country_id,i_rec.country_name));   

  END LOOP;

  RETURN;

END;

/

--4 : query using pipeline function : 

select * from table(fun_get_country_details());

select * from table(fun_get_country_details()) where country_id='IT'