Home > SQL, Technical > Transform from vertical rows to horizontal columns in oracle sql

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
  1. July 2, 2013 at 1:10 pm

    You can also use the PIVOT operator..

  2. July 3, 2013 at 1:59 am

    Hi Chrish,
    i dont have knowledge on PIVOT operator. I will try to learn about it. thanks for your comment.

    Thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: