playing around views

hi
I just want to be sure there is no way to do it inside the DB:
I have a view which return 1000 numbers and amounts like this:
Number Amount
000 234
001 3456
... ...
999 464
I have only two very long columns. is it possible to re-arrange this into a
view to get something like this:
number amount number amount number amount
000 345 001 9861 002 865
003 4564 004 45 005 9865
thks.On Tue, 25 Jan 2005 09:05:03 -0800, Kenny M. wrote:

>I have a view which return 1000 numbers and amounts like this:
>Number Amount
>000 234
>001 3456
>... ...
>999 464
>I have only two very long columns. is it possible to re-arrange this into
a
>view to get something like this:
>number amount number amount number amount
> 000 345 001 9861 002 865
> 003 4564 004 45 005 9865
Hi Kenny,
This is a presentation task, typically done at the client. However, if you
really want to burden the server with it, try:
SELECT a.number, a.amount, b.number, b.amount, c.number, c.amount
FROM MyTable AS a
LEFT OUTER JOIN MyTable AS b
ON b.Number = a.Number + 1
LEFT OUTER JOIN MyTable AS c
ON c.Number = a.Number + 1
WHERE a.Number % 3 = 0
ORDER BY a.Number
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why to stress your server trying to do this?. Use your client app / reportin
g
tool or programming language.
use northwind
go
select
identity(int, 0, 1) as number,
0 as amount
into
t
from
sysobjects as a cross join sysobjects as b
delete t where number > 999
update t set amount = power(2, number % 8)
select
*
from
(
select
number, amount
from
t
where
number % 3 = 0
) as a
left join
(
select
number, amount
from
t
where
number % 3 = 1
) as b
on a.number = b.number - 1
left join
(
select
number, amount
from
t
where
number % 3 = 2
) as c
on b.number = c.number - 1
drop table t
go
AMB
"Kenny M." wrote:

> hi
> I just want to be sure there is no way to do it inside the DB:
> I have a view which return 1000 numbers and amounts like this:
> Number Amount
> 000 234
> 001 3456
> ... ...
> 999 464
> I have only two very long columns. is it possible to re-arrange this into
a
> view to get something like this:
> number amount number amount number amount
> 000 345 001 9861 002 865
> 003 4564 004 45 005 9865
> thks.
>
>