Archive

Archive for July, 2013

Transform from vertical rows to horizontal columns in oracle sql

Syntax:

————

select
Primary column,
max((case vertical_column_name when  ‘val1’
THEN  ‘display value’
else  null
end )) col1,

max((case vertical_column_name when  ‘val2’
THEN  ‘display value’
else  null
end )) col2,

max((case vertical_column_name when  ‘val3’
THEN  ‘display value’
else  null
end )) col3
from

table1 t1

where  conditions
group by primary_column;

Example:

—————

SELECT

st.roll_no,

max((case Phone_number_type when  ‘1’
THEN  Phone_number
else  null
end )) Land_Line_Number,

max((case Phone_number_type when  ‘2’
THEN  Phone_number
else  null
end )) Hand_Phone_Number

FROM

STUDENTS st

WHERE

st.rollno between 100 and 200

GROUP BY st.rollno;

 

 

 

 

Advertisements
Categories: SQL, Technical