Database
Enthusiast

## Transpose II

Dear All,

i have data

id col1

1 a
2 b
3 c
4 d

i wish to convert into

1 a b c d

Regards
Santosh
4 REPLIES
Enthusiast

## Re: Transpose II

plz let me know if you got the answer for this...
Thanks & Regards,
Venkatesh
venkatesh.gubba@gmail.com
Enthusiast

## Re: Transpose II

I am updating ur question
i have data

id col1

1 a
1 b
1 c
1 d

i wish to convert into

1 a b c d

is this fine?
Enthusiast

Enthusiast

## Re: Transpose II

Here is an example, how to do this, but the restriction is that you'll need a field, which describe link between row order and column, where you want place this.

--drop table t;
create table t(n int, d varchar(10));

insert into t(n,d) values (1,'a');
insert into t(n,d) values (1,'b');
insert into t(n,d) values (1,'c');
insert into t(n,d) values (1,'d');
insert into t(n,d) values (2,'a2');
insert into t(n,d) values (2,'b2');
insert into t(n,d) values (2,'c2');
insert into t(n,d) values (2,'d2');

select
max(n) as n,
max(case when row_n=1 then d end) as a,
max(case when row_n=2 then d end) as b,
max(case when row_n=3 then d end) as c,
max(case when row_n=4 then d end) as d
from
(select row_number() over(partition by n order by d) as row_n,t.* from t)t
group by
n