doesn't Mark's code above provide your solution?

any chance that you might provide some set up script and sample data pls...we can then easily cut and paste into SSMS to give you a tried and tested answer.

please...

which reply was "grate" ??

does this work...

would be interested to learn about the requirement for this....have you over simplified the process?

--- Look up "Tally Table.....http://www.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden

SELECT ID AS RecordID,

...

a slightly different take on this.....

expanded the DDL that Sean provided

USE [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Forum]') AND type in (N'U'))

DROP TABLE [dbo].[Forum]

GO

IF EXISTS...

pwalter83 (1/23/2012)

---

Thanks for your reply, however, I do not want to create a new table from scratch. I only wish...

Click "Create" on the top menu bar/ribbon....select "Table"

the answer is already in the quote you used

"joining such many tables may degrade the performance"

...it all depends.

Do all the tables have indexes that are well maintained and cover...

another idea.....

DECLARE @t TABLE (companyname VARCHAR(10), amount int)

INSERT @t

VALUES ('xxx', 10),

('yyy', 8),

('zzz', 9),

('unknown', 3)

select 100- CAST (amount/((select SUM(amount) from @t)/100.0) as decimal (5,2)) as AllocatedCharge_percentageoftotal

from @t

where companyname =...

I am not looking for...

Hi

based on the example data you originally provided...can you please tell us what your expected results are??....hopefully this will clarify for all of us.

regards

think you have been around here long enough to know that some table scripts / data and expected results will assist all of us 🙂

mick burden (1/20/2012)

---

The table is part of a larger table and is used for other things, I just want to be able extract the piece of data I explained in...

can you please explain the business reason why cannot you store your data as below

this will treat you better long term, rather than someone conjuring up some dynamic code for...

think this works...its horrible....and your data doesnt match your expected results (Jim)

the question is why are you storing your data this way (excel as source ???)

WITH CTE

...