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?
- Are ids guaranteed to be consecutive (no gaps between them)?Quassnoi– Quassnoi2011-07-18 15:29:57 +00:00Commented Jul 18, 2011 at 15:29
- 1Please describe in more detail what you are trying to achieve and also which version of SQL you are using.Barry Kaye– Barry Kaye2011-07-18 15:31:06 +00:00Commented Jul 18, 2011 at 15:31
- IDs are consecutive starting from 1 and no gap. I use SOL Server 2005kumar– kumar2011-07-18 17:28:05 +00:00Commented Jul 18, 2011 at 17:28
- If your sequence is without gaps, @t-clausen.dk solution is more appropriate.Kirill Polishchuk– Kirill Polishchuk2011-07-18 17:36:22 +00:00Commented Jul 18, 2011 at 17:36
Add a comment |
2 Answers
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 answered Jul 18, 2011 at 15:33
Kirill Polishchuk
56.4k11 gold badges125 silver badges127 bronze badges
5 Comments
t-clausen.dk
Left join instead of join perhaps ? Try with 5 rows like in my example.
Kirill Polishchuk
@t-clausen.dk, Sure, but it may depend on task... I updated my answer :-)
t-clausen.dk
It was only fair you got the credit, since there was to information about the ID being without gabs and I just assumed there wasn't. You code takes that into account. But without gabs he should use my solution, because of performance (as you mentioned).
Kirill Polishchuk
@t-clausen.dk, Yep I wrote OP that.
kumar
t-clausen.dk solution also good as long as there is no gap. But the other solution I selected at least protects from such type of bug just in case there is a gap in IDs.
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