Transpose columns to row in a single query

Database

Transpose columns to row in a single query

Hi

Below is the source data:

A B

1 4

2 5

3 6

 I need the target data as:

X Y Z

1 2 3

4 5 6

How can I achieve this in a single SQL, without creating any temporary table?

2 REPLIES

Re: Transpose columns to row in a single query

MOUSUSI,

sel

 max(case when rn=1 then ref.a end) as X,

         max(case when rn=2 then ref.a end)  as y,

         max(case when rn=3 then ref.a end)  as z    

from

(

sel

a,b

,

row_number() over ( order by a,b )rn

from temp1

)

ref

UNION

sel

         max(case when rn=1 then ref.B end) as X,

         max(case when rn=2 then ref.B end)  as y,

         max(case when rn=3 then ref.B end)  as z    

from

(

sel

a,b

,

row_number() over ( order by a,b )rn

from temp1

)

ref

Re: Transpose columns to row in a single query

You can try with select tdstats.udfconcat(trim(a)) from tbl and union all with a select  tdstats.udfconcat(trim(b)). It will come in one column with quotes and comma. You can  do regexp_replace for quotes and then split values delimited by ,