This forum is currently locked. You can't register or post questions at this time. (read more)

Select Statement, Converting Column into Row and vise versa.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

vhage30
Member
Posts: 6
Joined: Tue Jan 07, 2014 12:52 am

Select Statement, Converting Column into Row and vise versa.

Postby vhage30 » Wed Jan 22, 2014 2:37 am

Hello All,

Good day.

I just want to ask if it is possible to make your Column into a row and your row into a column in a select statement?

I have made this query below.

Code: Select all

select
(select mstd.name from mqcstdparam mstd where mstd.oid = t.paramname) ParamName,
spc.ul,
spc.arul,
spc.arll,
spc.ll,
spc.target
FROM
DQCPARAMETERSPEC SPC
inner join
(select
dqc.oid DQCOID,
dqc.qcstdparamoid ParamName,
chk.boxorder ParamOrder
from
DQCPARAMETERVERSION DQC
inner join DQCCHECKSHEET  chk on dqc.qcstdparamoid = chk.mqcstdparamoid
where chk.dqcchecksheetversionoid = '36bf4107-52bd-43d1-8e99-a24300d97a2f'
and dqc.operationoid = '610'
and dqc.spitemoid = 'd9299083-9530-4069-8ba5-ffddc0213b79') t
on t.DQCOID = spc.dqcparameterversionoid
order by to_number(t.paramorder) asc


The query above show the following result.
ParamName__________UL______________ARUL__________ARLL__________LL___________Target
Short Side..............<=87.75..............<=87.743.........>=87.687.........>=87.68..........=87.715
Long Side...............<=174.8651.........<=174.8581......>=174.8021.......>=174.7951.....=174.8301
Diameter...............<=8.1417.............<=8.1347........>=8.0787..........>=8.0717........=8.1067


What I want to do is to show the data in this kind of presentation.
_________________Short Side___________Long Side_____________Diameter
UL.....................<=87.75..................<=174.8651................<=8.1417
ARUL..................<=87.743................<=174.8581................<=8.1347
ARLL..................>=87.687................>=174.8021.................>=8.0787
LL.....................>=87.68..................>=174.7951.................>=8.0717
Target................=87.715..................=174.8301..................=8.1067


I have seen query samples like Pivot and Crosstab but they are not dynamic.

Any suggestion and help will be much appreciated.

Thank you very much.. :D

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Select Statement, Converting Column into Row and vise ve

Postby Tim... » Wed Jan 22, 2014 3:27 pm

Hi.

Unfortunately, there are no built-in dynamic ways to do this. The PIVOT and UNPIVOT operators are cool, but they require some certain elelments to be knows at the time of coding. They are not dynamic.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

vhage30
Member
Posts: 6
Joined: Tue Jan 07, 2014 12:52 am

Re: Select Statement, Converting Column into Row and vise ve

Postby vhage30 » Fri Jan 24, 2014 4:20 am

Mr. Tim,

Thank you for the response. I think I will just do the manipulation on the system that I`m creating.

Again thank you very much.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Select Statement, Converting Column into Row and vise ve

Postby Tim... » Fri Jan 24, 2014 7:38 am

OK. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests