All Forums Database
vaishalishetty 1 post Joined 11/13
10 Nov 2013
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!

Raja_KT 1246 posts Joined 07/09
10 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
11 Nov 2013

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

Raja_KT 1246 posts Joined 07/09
11 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

gerardo 13 posts Joined 10/09
12 Nov 2013

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
 
 

M.Saeed Khurram 544 posts Joined 09/12
14 Nov 2013

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

You must sign in to leave a comment.