Hi,

I have a table of this format..

id, value1, value2, rate
1 1 2M 2
2 1 3M 33
3 1 4M 44
4 2 1M 111
5 2 2M 222
6 2 3M 333

I want to have a table that looks like this

id value1 1M 2M 3M 4M total
1 1 0 2 33 44 79
2 1 111 222 333 0 366


Any suggestions?

Note: the numer of M's is constant, not dynamic.

Hi!!

I think something like this will work:

select
value1,
'1M'=sum(1*charindex('1M',value2)*rate),
'2M'=sum(1*charindex('2M',value2)*rate),
'3M'=sum(1*charindex('3M',value2)*rate),
'4M'=sum(1*charindex('4M',value2)*rate),
'Total'=sum(rate)
from t1
group by value1

I don't think you need the "id" column in your result set ...

Good Luck!

Sara ...

dufffman@gmail.com wrote:
> Hi,
>
> I have a table of this format..
>
> id, value1, value2, rate
> 1 1 2M 2
> 2 1 3M 33
> 3 1 4M 44
> 4 2 1M 111
> 5 2 2M 222
> 6 2 3M 333
>
> I want to have a table that looks like this
>
> id value1 1M 2M 3M 4M total
> 1 1 0 2 33 44 79
> 2 1 111 222 333 0 366
>
>
> Any suggestions?
>
> Note: the numer of M's is constant, not dynamic.