[b]data in myTable "hr"[/b]
[COLOR=Blue](n) m top[/COLOR]
(1) 1 earth
(2) 1 North America
(3) 2 America
(4) 1 Europe
(5) 1 Asia
(6) 4 Germany
(7) 2 Canada
(8) 3 New York
(9) 5 Korea
(10) 1 Africa
I have data in myTable "ht" like the above.
[b]code[/b]
[COLOR=Red]<cfoutput>[/COLOR]
<cfquery datasource='gh01' name='m1'>
select n,top from hr
where m=1 and n<>1
</cfquery>
[COLOR=Blue]<cfloop query='m1'>[/COLOR]
#top#<br>
<cfquery datasource='gh01' name='m2'>
select n,top from hr
where m=#m1.n#
</cfquery>
[COLOR=Green]<cfloop query='m2'>[/COLOR]
#top#<br>
<cfquery datasource='gh01' name='m3'>
select n,top from hr
where m=#m2.n#
</cfquery>
[COLOR=Red]<cfloop query='m3'>[/COLOR]
#top#<br>
[COLOR=Red]</cfloop>[/COLOR]
<br>
[COLOR=Green]</cfloop>[/COLOR]<br>
[COLOR=Blue]</cfloop>[/COLOR]
[COLOR=Red]</cfoutput>[/COLOR]
The code above produces the result below.
[b]result[/b]
North America
America
New York
Canada
Europe
Germany
Asia
Korea
Africa
I like to produce my target result like the following.
[b]target result[/b]
North America Europe Asia Africa
America Canada Germany Korea
New York
The following code is one of my trials, but it produces too many records.
[b]trial code[/b]
select t1.top as t1_top
, t2.top as t2_top
, t3.top as t3_top
, t4.top as t4_top
from hr t1
left outer join hr t2 on t2.m=t1.n
left outer join hr t3 on t3.m=t2.n
left outer join hr t4 on t4.m=t3.n
If the structure of mytable "hr" is not proper for getting my target result, please tell me what is the proper structure and data for getting my target result.
