Move column data as rows

Database

Move column data as rows

Hi,

Can you please tell me how can I convert the data which is in multiple columns to 1 single row?

Original format:




ID NAME VALUE
123 A 1
123 B 2
123 C 3
123 D 4
123 E 5

To be converted like this:




ID A B C D E
123 1 2 3 4 5

Appreciate any help!

Thanks!

5 REPLIES
Enthusiast

Re: Move column data as rows

You select a field from the table union all with the second field and union all with the third field. You can convert the data type to the same for all. You can select distinct for the one you want.

Cheers,

Raja

Enthusiast

Re: Move column data as rows

Your data output is not understandable, Can you please give some more description? and also paste the data in correct format?

Like currently, it seems that you want to see this data in a coolumn!

Khurram
Enthusiast

Re: Move column data as rows

Hi,

  create table then select 

The query:

SELECT NAME as ID FROM tabl1---note here I want to  get the col as you want

UNION ALL

SELECT DISTINCT TO_CHAR(ID) FROM tabl1

UNION ALL

SELECT VALUE FROM tabl1

Result:

ID

A

B

C

D

E

123

1

2

3

4

Cheers,

Raja

Teradata Employee

Re: Move column data as rows

try,

select id,

max(case when name ='a' then value else null end) as A,

max(case when name ='b' then value else null end) as B,

..

from  table

group by 1

Regards

Gerardo

Enthusiast

Re: Move column data as rows

Hi,

If you want to put data in all columns into a single column then the technique advised by Raja will obviously work. But if you want to place the data in these column into a single row then you might have to transpose you data.

Khurram