February 18, 2010 at 8:42 am
I have a table that has a field with a dollar amount and another field that has a list of items. I need to count the number of items in the list field and create the same number of rows, dividing the dollar amount by the number of items in the lista and distribute that cost to each item. The list length can vary. The separator is "&". Does anyone have any suggestions?
Example: Cost List
600 A & B & C
Final output needs to be
Cost List
200 A
200 B
200 C
Thanks
Sandy
February 18, 2010 at 10:23 am
May I suggest you read this article by Jeff Moden ... it has a good deal of sample code, and will assist you in solving your problem.
February 23, 2010 at 10:04 pm
You can also have a look at http://qa.sqlservercentral.com/Forums/Topic870949-338-1.aspx
which has the solution to a similar problem
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 23, 2010 at 10:31 pm
declare @S varchar(1000), @data int
I think below statment can use full for yur case
select @S = '600 A & B & C '
select @data =rtrim( substring( @S,0,charindex('&',@s)-2))
select @S=Substring(@s,len(@data)+1, len(@s)-len(@data))
;with csvtbl(i,j)
as
(
select i=1, j=charindex('&',@s+'&')
union all
select i=j+1, j=charindex('&',@s+'&',j+1) from csvtbl
where charindex('&',@s+'&',j+1) <> 0
)
select @data/(select count('x') from csvtbl ),substring(@s,i,j-i)
from csvtbl
If U want to result for each row then , U can use function to get Appropriate result
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply