# Re: Need help to construct a Query

**BluShadow**Nov 11, 2013 5:48 AM

937357 wrote:

Hi Experts,

Could you please helme to get required output.

TEST

Source Col 1Col 2Col 3Col 4Row 1r1*c1 r1*c2 r1*c3 r1*c4 Row 2r2*c1 r2*c2 r2*c3 r2*c4

select 'r1 * c1' Col1,'r1 * c2' Col2,'r1 * c3' Col3, 'r1 * c4' Col4 from dual union all

select 'r2 * c1' Col1 ,'r2 * c2' Col2,'r2 * c3' Col3,'r2 * c4'Col4 from dual

Output:

TEST

Target Col 1Col 2Col 3Col 4Row 1r1 * c1 r2 * c1 r1 * c3 r2 * c3 Row 2r1 *c2 r2 * c2 r1 * c4 r2 * c4

Thanks in Advance.

Regards

Cheers.

Your question makes no sense.

You output appears to show that col2 and col4 of row 1 and being swapped with col1 and col3 of row 2 respectively, yet your test data has no way of determining which row of data is "row 1" and which is "row 2" as there's nothing in the data to tell us that.

You also haven't explained what the logic is for swapping these bits of data about, or what database version you are using (answers can vary greatly depending on which database version you are using).

Read this:Re: 2. How do I ask a question on the forums?

and post sufficient details for people to help you.

We could easily come up with something like:

SQL> ed

Wrote file afiedt.buf1 with t as (select 1 as r, 'r1 * c1' Col1,'r1 * c2' Col2,'r1 * c3' Col3, 'r1 * c4' Col4 from dual union all

2 select 2, 'r2 * c1' Col1 ,'r2 * c2' Col2,'r2 * c3' Col3,'r2 * c4'Col4 from dual

3 )

4 --

5 -- end of test data

6 --

7 select l2 as r

8 ,max(decode(rn,1,col)) as col1

9 ,max(decode(rn,3,col)) as col2

10 ,max(decode(rn,2,col)) as col3

11 ,max(decode(rn,4,col)) as col4

12 from (

13 select mod(l+1,2)+1 as l2

14 ,row_number() over (partition by mod(l+1,2)+1 order by ((r-1)*4)+l) as rn

15 ,decode(l,1,col1,2,col2,3,col3,col4) as col

16 from t cross join (select level l from dual connect by level <= 4)

17 )

18 group by l2

19* order by 1

SQL> /

R COL1 COL2 COL3 COL4

---------- ------- ------- ------- -------

1 r1 * c1 r2 * c1 r1 * c3 r2 * c3

2 r1 * c2 r2 * c2 r1 * c4 r2 * c4

But if we don't know exactly what you're after, this may or may not meet your requirements.