Oracle.Cheat.Book: Convert Row To Column

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

COLUMN employees FORMAT A50


--Here is the interview question.
--Write a sql query to transpose rows to columns.

--SQL to create the table
Create Table Countries
(
Country varchar2(50),
City varchar2(50)
);

Insert into Countries values ('USA','New York');
Insert into Countries values ('USA','Houston');
Insert into Countries values ('USA','Dallas');

Insert into Countries values ('India','Hyderabad');
Insert into Countries values ('India','Bangalore');
Insert into Countries values ('India','New Delhi');

Insert into Countries values ('UK','London');
Insert into Countries values ('UK','Birmingham');
Insert into Countries values ('UK','Manchester');

--Using PIVOT operator we can very easily transform rows to columns.
Select Country, City1, City2, City3
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3)
) Piv

Other Resources

Web