asked on

pivoting in sql server columns to rows

we have this sql (sql server)

select c.SAMPLE_NUMBER, f.AMOUNT
from daily_arg c
left outer join daily_results f on c.arg_GUID = f.arg_GUID
and f.RANK = 0
where c.PROJECT_NUMBER = '99-9999'
and c.SAMPLE_NUMBER is not null
order by c.SAMPLE_NUMBER

and the out put is like this

1 $11
2 $435.57
3 $233
4 $12
5 $55
6 $69
7 $4.68
8 $65
9 $45
10 $1.08
11 $21
12 $4.50

=====
and we want the output to look like this

1 $11 2 $435.57 3 $233 4 $12
5 $55 6 $69 7 $4.68 8 $65
9 $45 10 $1.08 11 $21 12 $4.50

4 entries for each row how can we achieve this

xizwyck

This has what you need. Just adapt the SQL for your needs:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

it-rex

ASKER

I have seen all these ,
but still could not do it.

Emes

This may get you closer

select c.SAMPLE_NUMBER, sum(f.AMOUNT)
from daily_arg c
left outer join daily_results f on c.arg_GUID = f.arg_GUID
and f.RANK = 0
where c.PROJECT_NUMBER = '99-9999'
and c.SAMPLE_NUMBER is not null
order by c.SAMPLE_NUMBER
group by
c.SAMPLE_NUMBER/4,c.SAMPLE_NUMBER % 4

cyberkiwi

This will do it

;with tmp as (
      select c.SAMPLE_NUMBER, f.AMOUNT, rn=row_number() over (order by c.SAMPLE_NUMBER)
      from daily_arg c
      left outer join daily_results f on c.arg_GUID = f.arg_GUID
      and f.RANK = 0
      where c.PROJECT_NUMBER = '99-9999'
      and c.SAMPLE_NUMBER is not null
)
select a.sample, a.amount, b.sample, b.amount, c.sample, c.amount, d.sample, d.amount
from tmp a
left join tmp b on b.rn=a.rn+1
left join tmp c on c.rn=a.rn+2
left join tmp d on d.rn=a.rn+3
where a.rn % 4 = 1
order by a.rn

but... why! This should be done from the front end...
Your call

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

it-rex

ASKER

Amazing;
thanks alot