CODEWITHSUNDEEP
sql-servert-sql
kumar
kumar

Reputation: 107

T-SQL data transformation

I have a result set like this:

ID  Value
1    100
2    50
3    200
4    30
-    -
-    -

I want it to transform into following:

Value1 Value2
100      50
200      30
-        -
-        -

How to do it with T-SQL?

Upvotes: 1

Views: 231

Answers (2)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56222

Use this:

select a.Value, b.Value
from
(
    select row_number() over(order by ID) [rn], Value
    from @t
)a
left join
(
    select row_number() over(order by ID) [rn], Value
    from @t
)b on b.rn = a.rn + 1
where a.rn % 2 = 1

Sample data:

declare @t table (ID int, Value int)

insert @t values (1,100), (2,50), (3,200), (4,30)

Output:

Value       Value
----------- -----------
100         50
200         30

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

declare @t table (id int, v int)

insert @t values (1, 10)
insert @t values (2, 20)
insert @t values (3, 30)
insert @t values (4, 40)
insert @t values (5, 50)


select t1.v, t2.v
from @t t1 
left join @t t2 
on t1.id + 1 = t2.id
where t1.id %2 = 1

Upvotes: 2

Related Questions