thanks for the reply. the problem is the number of bom names used in the query is dynamic ie., the product names are not limited to 2. even the query...

I don't want to use CASE statement. I'd like to know how to do this using PIVOT.

This one is more accurate >>

Select i.I_ID,ItemName,Active,Commission

From #item I INNER JOIN #commission C

on CHARINDEX(','+RTRIM(CAST(I.I_ID as char(4))),c.I_ID)>0

Where I.active=1

Select i.I_ID,ItemName,Active,Commission

From #item I INNER JOIN #commission C

on CHARINDEX(RTRIM(CAST(I.I_ID as char(4))),c.I_ID)>0

Where I.active=1

Thanks guys

thanks a lot, will chk out.

Hi,

What is MDX app? could you explain me a bit more on how to do that?

Thanks,

Hari

thanks Steve, let me chk this out and get back you. Thanks a lot

Hari

Thanks Steve.

What is the best way to debug this issue?

Select branchname,count(distinct(transactionid)),count(distinct(id)),

sum(pass) as pass,sum(fail) as fail 

from (

   select b.name as branchname, t.name as tranname,

   i.transactionid, i.id,

   case pass when 1 then 1 else 0 end as...

Hi Paul,

Thanks for your reply, as u said your query will work only for one level.  Though it works for the sample data, it'll fail if there is another level...

Hi Rajesh,

This is the efficient solution that I can think of coz i used only one loop. if anyone else can come out with a better one, I'll happy to...

Hi,

Is the Pkt_type and pkt_id is fixed (1 to 4)?

~hari

Thanks guys for taking time to answer my question.

Rob,

Agreed to your point on the wsequence.. since the reset point is not mentioned in the question, i assumed that it'll be reset for each page. that is why i...