I have a table with only one column about 100 rows of only names. But I need to display the 3 names in a row. So that I will get 34 rows each row with 3 names.
Example:
Name
_____
Raj
Sam
Guru
Tej
Avin
Sami
Fanst
I need to display above data as
Name Name1 Name2
____ _____ ______
Raj Sam Guru
Tej Avin Sami
Fanst
No condition just need to covert single column value into 3 columns data.
Oracle DB
You can do this using conditional aggregation and rownum. Something like this:
select max(case when mod(rn, 3) = 1 then name end) as name1,
max(case when mod(rn, 3) = 2 then name end) as name2,
max(case when mod(rn, 3) = 0 then name end) as name3
from (select name, rownum as rn
from table t
) t
group by trunc((rn - 1) / 3);
You can do it using CASE, make a try on it using PIVOT
Try to PIVOT Your Name column like this
SELECT Name,Name1,Name2
FROM
(SELECT Name FROM table_name) AS SourceTable
PIVOT
(
FOR Name IN (Name,Name1,Name2)
) AS PivotTable;
PIVOT in this situation as you would not know the values to specify in advance.