Transpose columns to rows

, ,
1

Hi ,

i have this data sample -

id | value
1,4
1,3
1,5
2,3
2,4
2,7
2,8

i want in sql query to change it to this -

1,4,3,5,null
2,3,4,7,8

I have no pivot so i need pure sql .

How can i do it ?

2

so what are the rules here? help us help you. also please always add data as follows, otherwise people will ignore your question.

create table #orcohen(id int, value int)

insert into #orcohen
select 1,4 union
select 1,3 union
select 1,5 union
select 2,3 union
select 2,4 union
select 2,7 union
select 2,8

drop table #orcohen

3

ok thanks

4

Using what @yosiasz has would this be what you are looking for?

SELECT B.ID, STUFF((SELECT  ', ' + CAST(A.value AS VARCHAR(10))
                                  FROM #orcohen A 
                                  WHERE A.ID = B.ID 
                                  FOR XML PATH('')),1,1,'') As myvalue
FROM #orcohen B
GROUP BY B.ID;

or are you looking to pivot the data?

1 Like
5 
select id,
    max(case when row_num = 1 then value end) as value1,
    max(case when row_num = 2 then value end) as value2,
    max(case when row_num = 3 then value end) as value3,
    max(case when row_num = 4 then value end) as value4,
    max(case when row_num = 5 then value end) as value5,
    max(case when row_num = 6 then value end) as value6,
    max(case when row_num = 7 then value end) as value7,
    max(case when row_num = 8 then value end) as value8,
    max(case when row_num = 9 then value end) as value9
    --...
from (
    select *, row_number() over(partition by id order by (select null)) as row_num
    from #orcohen
) as derived
group by id
6

Hi ,

I cant use order by select null , how can i rewrite it ?

7 
select *, row_number() over(partition by id order by id) as row_num
from #orcohen