After I posted the last bit I had ideas of improving the code like

With PivotedCTE(Group1,Group2,Group3,Group4) as

(select coalesce([1],0) as Group1,coalesce([2],0) as Group2,coalesce([3],0) as Group3,coalesce([4],0) as Group4 from

(Select iGroup,id from SivaTest) ...

After the test Livingstone did I changed my code so that it does not scan twice and this is the code

With GroupCTE(iGroup,nCount) as

(SELECT igroup,COUNT(id) FROM SivaTest group by igroup),

PivotedCTE(Group1,Group2,Group3,Group4) as

(select...

yes I have but I dont know how to get the stats etc

I get your point.

I will try

What I meant was that it didnt read it twice because of my Group and Pivot policy

It read twice because of the UNION

if Livinstone will let me have the stats...

What I would like to know Livingstone is if everything imroves if we take the UNION out

I think then it will half in time

If it did that i can suggest...

As I said before my background is from else where

Can you explin to me what the stats shows ?

are you saying my method was faster even with the UNION ?

If effeincey was the objective the C++ lanaguage would have been kicked in the first place. All object oriented lanaguages added so much to burden for the application. But we...

I may be defensive but I am commited to my methods. No one is going to changing that

SQL Databse was based on Matahematical principles. The solutions we are suggesting are...

I am pursuing my methods and developing applications

The T SQL Logic which every one seems to want is effient code

What they dont seem to reaalise is that it is becoming...

Ok I get it now. You had posted the solution earlier on which I had not tested or Sree had accepted

The thread continued for another 2 pages after my...

Then and now, I try to solve the problem mathematically and then write the TSQL

That would always get a solution but might be heavy on resources

yes I have read it quickly but will read it at leisure sometime

However there was a topic few days back and that was my first time on this site and...

I accept that the first method is best

Can we leave it at that

Line by Line code in Applications software is not thought of as good practice. It must be done in logical For next Loops etc and broken down niceley

I agree...