Home » SQL & PL/SQL » SQL & PL/SQL » Concat (Oracle 11g)
icon9.gif  Concat [message #577738] Wed, 20 February 2013 01:07 Go to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi

I am having below query
select C1Y56CH02 || '$|' || C1Y56CH03 || '$|' || C1Y56CH04 || '$|' || C1Y56CH08
		|| '$|' || C1Y56CS09 || '$|' || C1Y56CS03 || '$|' || C1Y56CS04 || '$|' || C1Y56CS05 || '$|' || C1Y56CS06
		|| '$|' || C1Y56CS07 || '$|' || C1Y56CS08 || '$|' || C1Y56CS10 || '$|' || C1Y56CS11 || '$|' || C1Y56CS12
		|| '$|' || C1Y56CS13 || '$|' || C1Y56CS15 || '$|' || C1Y56CD05 || '$|' || C1Y56CD13 || '$|' || C1Y56CD04
		|| '$|' || C1Y56CD14 || '$|' || C1Y56CD06 || '$|' || C1Y56CD11 || '$|' || C1Y56CD07 || '$|' || C1Y56CD10
		|| '$|' || C1Y56CD12, C1BCT  
from
(select	sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT  
from	(
select	C1BCT as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT 
from	CRPDTA.F56055C1 A 
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)

union

select	C1BCT+iteration.rn+1 as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT 
from	CRPDTA.F56055C1 r_outer, (	select	rownum rn 
									from	dual connect by level < (	select	count(*) 
																		from	(	select	C1BCT, trim(C1Y56CH01)
																					from	CRPDTA.F56055C1 A 
																					where	C1Y55ELM01 = 'REP_ABSO' 
																					and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
																					and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
																				) t1)
																	) iteration
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
and		trim(C1Y56CH01)='B'
and		(	select	count(C1Y56CH01) 
			from	(	select	C1BCT, C1Y56CH01
						from	CRPDTA.F56055C1 A 
						where	C1Y55ELM01 = 'REP_ABSO' 
						and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
						and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_inner 
			where	trim(r_inner.C1Y56CH01)='D' 
			and		r_inner.C1BCT>r_outer.C1BCT 
			and		r_inner.C1BCT<(	select	nvl(MIN(C1BCT), 99999999) as mincolindex
									from	(	select	C1BCT, C1Y56CH01
												from	CRPDTA.F56055C1 A 
												where	C1Y55ELM01 = 'REP_ABSO' 
												and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
												and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_min 
                                    where	trim(r_min.C1Y56CH01)='B' 
                                    and		r_min.C1BCT>r_outer.C1BCT))>iteration.rn
order
by  sortorder) withoutA where C1Y56CH01<>'A'

union

select	withA1.sortorder, trim(withA.C1Y56CH01) as C1Y56CH01, trim(withA.C1Y56CH02) as C1Y56CH02, trim(withA.C1Y56CH03) as C1Y56CH03, trim(withA.C1Y56CH04) as C1Y56CH04, trim(withA.C1Y56CH08) as C1Y56CH08
		, trim(withA.C1Y56CS09) as C1Y56CS09, trim(withA.C1Y56CS03) as C1Y56CS03, trim(withA.C1Y56CS04) as C1Y56CS04, trim(withA.C1Y56CS05) as C1Y56CS05, trim(withA.C1Y56CS06) as C1Y56CS06
		, trim(withA.C1Y56CS07) as C1Y56CS07, trim(withA.C1Y56CS08) as C1Y56CS08, trim(withA.C1Y56CS10) as C1Y56CS10, trim(withA.C1Y56CS11) as C1Y56CS11, trim(withA.C1Y56CS12) as C1Y56CS12
		, trim(withA.C1Y56CS13) as C1Y56CS13, trim(withA.C1Y56CS15) as C1Y56CS15, trim(withA.C1Y56CD05) as C1Y56CD05, trim(withA.C1Y56CD13) as C1Y56CD13, trim(withA.C1Y56CD04) as C1Y56CD04
		, trim(withA.C1Y56CD14) as C1Y56CD14, trim(withA.C1Y56CD06) as C1Y56CD06, trim(withA.C1Y56CD11) as C1Y56CD11, trim(withA.C1Y56CD07) as C1Y56CD07, trim(withA.C1Y56CD10) as C1Y56CD10
		, trim(withA.C1Y56CD12) as C1Y56CD12, withA.C1BCT  
from	(select	C1BCT as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT 
from	CRPDTA.F56055C1 A 
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)

union

select	C1BCT+iteration.rn+1 as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT
from	CRPDTA.F56055C1 r_outer, (	select	rownum rn 
									from	dual connect by level < (	select	count(*) 
																		from	(	select	C1BCT, trim(C1Y56CH01)
																					from	CRPDTA.F56055C1 A 
																					where	C1Y55ELM01 = 'REP_ABSO' 
																					and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
																					and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
																				) t1)
																	) iteration
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
and		trim(C1Y56CH01)='B'
and		(	select	count(C1Y56CH01) 
			from	(	select	C1BCT, C1Y56CH01
						from	CRPDTA.F56055C1 A 
						where	C1Y55ELM01 = 'REP_ABSO' 
						and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
						and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_inner 
			where	trim(r_inner.C1Y56CH01)='D' 
			and		r_inner.C1BCT>r_outer.C1BCT 
			and		r_inner.C1BCT<(	select	nvl(MIN(C1BCT), 99999999) as mincolindex
									from	(	select	C1BCT, C1Y56CH01
												from	CRPDTA.F56055C1 A 
												where	C1Y55ELM01 = 'REP_ABSO' 
												and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
												and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_min 
                                    where	trim(r_min.C1Y56CH01)='B' 
                                    and		r_min.C1BCT>r_outer.C1BCT))>iteration.rn
order
by  sortorder) withA, (select	C1BCT as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT
from	CRPDTA.F56055C1 A 
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)

union

select	C1BCT+iteration.rn+1 as sortorder, trim(C1Y56CH01) as C1Y56CH01, trim(C1Y56CH02) as C1Y56CH02, trim(C1Y56CH03) as C1Y56CH03, trim(C1Y56CH04) as C1Y56CH04, trim(C1Y56CH08) as C1Y56CH08
		, trim(C1Y56CS09) as C1Y56CS09, trim(C1Y56CS03) as C1Y56CS03, trim(C1Y56CS04) as C1Y56CS04, trim(C1Y56CS05) as C1Y56CS05, trim(C1Y56CS06) as C1Y56CS06
		, trim(C1Y56CS07) as C1Y56CS07, trim(C1Y56CS08) as C1Y56CS08, trim(C1Y56CS10) as C1Y56CS10, trim(C1Y56CS11) as C1Y56CS11, trim(C1Y56CS12) as C1Y56CS12
		, trim(C1Y56CS13) as C1Y56CS13, trim(C1Y56CS15) as C1Y56CS15, trim(C1Y56CD05) as C1Y56CD05, trim(C1Y56CD13) as C1Y56CD13, trim(C1Y56CD04) as C1Y56CD04
		, trim(C1Y56CD14) as C1Y56CD14, trim(C1Y56CD06) as C1Y56CD06, trim(C1Y56CD11) as C1Y56CD11, trim(C1Y56CD07) as C1Y56CD07, trim(C1Y56CD10) as C1Y56CD10
		, trim(C1Y56CD12) as C1Y56CD12, C1BCT
from	CRPDTA.F56055C1 r_outer, (	select	rownum rn 
									from	dual connect by level < (	select	count(*) 
																		from	(	select	C1BCT, trim(C1Y56CH01)
																					from	CRPDTA.F56055C1 A 
																					where	C1Y55ELM01 = 'REP_ABSO' 
																					and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
																					and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
																				) t1)
																	) iteration
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
and		trim(C1Y56CH01)='B'
and		(	select	count(C1Y56CH01) 
			from	(	select	C1BCT, C1Y56CH01
						from	CRPDTA.F56055C1 A 
						where	C1Y55ELM01 = 'REP_ABSO' 
						and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
						and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_inner 
			where	trim(r_inner.C1Y56CH01)='D' 
			and		r_inner.C1BCT>r_outer.C1BCT 
			and		r_inner.C1BCT<(	select	nvl(MIN(C1BCT), 99999999) as mincolindex
									from	(	select	C1BCT, C1Y56CH01
												from	CRPDTA.F56055C1 A 
												where	C1Y55ELM01 = 'REP_ABSO' 
												and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
												and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_min 
                                    where	trim(r_min.C1Y56CH01)='B' 
                                    and		r_min.C1BCT>r_outer.C1BCT))>iteration.rn
order
by  sortorder) withA1 
where withA.C1Y56CH01='A' and withA1.C1Y56CH01='B' 
order by 1, 2
)


results

http://i.imgur.com/16uVEx6.png

The output is coming in three different line like row 1 is A , row 2 is B, row 3 is D then row 4 is A , row 5 is B row 6 is D and so on

Requirement :

Our requirement is like can we concat first 3 rows in single row having data of combination of ABD and then next row 2 will have next combination of ABD and so on
Re: Concat [message #577742 is a reply to message #577738] Wed, 20 February 2013 01:18 Go to previous messageGo to next message
michael_bialik
Messages: 602
Registered: July 2006
Senior Member
Look at LIST_AGG function
Re: Concat [message #577745 is a reply to message #577742] Wed, 20 February 2013 01:34 Go to previous message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean LISTAGG.

Regards
Michel
Previous Topic: Decode logic not working
Next Topic: How to handle NULL while inserting records in a table in PL/SQL?
Goto Forum:
  


Current Time: Thu Jul 31 08:30:07 CDT 2014

Total time taken to generate the page: 0.12205 seconds