Prev: Connection string to SQL Server to 2005
Next: tsql to change oracle pswd
From: Erland Sommarskog on 22 Dec 2009 18:14
RedGlow06 (u57010(a)uwe) writes:
> I have a field that contains values like "1,3,0,0" and "2,0,0" and "1". I
> need to sum those values within the field. The field can have up to 100
> numbers seperated by commas. How can I do sum them?
>
> so in example 1 "1,3,0,0" I would want it to say 4
> example 2 "2,0,0" I would want it to say 2
> example 3 "1" I would want it to say 1
>
> Any help is much appreciated. Thanks!

Look here for a number of methods:
http://www.sommarskog.se/arrays-in-sql.html


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: TheSQLGuru on 23 Dec 2009 14:29
There is a massive forum thread on sqlservercentral on string parsing,
complete with countless solutions to the problem and a dizzying array of
benchmarks.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"RedGlow06" <u57010(a)uwe> wrote in message news:a0fc0f89c2b74(a)uwe...
> It looks to be working ok except when I have 2 of the same values like
> "1,1,
> 0" shows up as 1 or "1,2,1,0" shows up as 3. Any suggestions?
>
> Bob Barrows wrote:
>>> I have a field that contains values like "1,3,0,0" and "2,0,0" and
>>> "1". I need to sum those values within the field. The field can
>>[quoted text clipped - 5 lines]
>>>
>>> Any help is much appreciated. Thanks!
>>
>>Given the creation of a table called Numbers:
>>********************************************************************
>>CREATE TABLE dbo.Numbers
>>(
>> Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
>>)
>>
>>WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
>>BEGIN
>> INSERT dbo.Numbers DEFAULT VALUES
>>END
>>********************************************************************
>>
>>This will do what you want:
>>select 1 as ID,'1,3,0,0' as vals into #tmp
>>union select 2,'2,0,0'
>>union select 3,'1'
>>
>>select id, vals,sum(number) as total
>>from (
>> select id,vals,Number
>> from #tmp as t , numbers as n
>> WHERE CHARINDEX
>> (
>> ','+CONVERT(VARCHAR(12),Number)+',',
>> ','+vals+','
>> ) > 0
>>) as q
>>group by id,vals
>>
>>Now you can read the rest of the answers which will undoubtedly start by
>>castigating the designer of this database for storing multiple pieces of
>>data in a single column, thus violating normal form and causing the
>>problem you are now experiencing.
>>
>


From: --CELKO-- on 23 Dec 2009 15:50
>> I have a field [sic: columns are not fields] that contains values like "1,3,0,0" and "2,0,0" and "1".  I need to sum those values within the field [sic].  The field [sic] can have up to 100 numbers separated by commas..  How can I do sum them? <<

BY DEFINITION a column has scalar values, this is not a valid value in
SQL. This violates a thing called First Normal Form (1NF) which you
will find in the first few chapters of a book on RDBMS.

You can find lots of stinking kludges to parse strings, but the right
answer is to design a proper schema and fire the guy who did this.

If you use a kludge, be sure to include code to raise all the errors
that a parameter in a procedure call can raise. The cowboy coders
never bother with that part ..
From: Tony Rogerson on 23 Dec 2009 16:22
> BY DEFINITION a column has scalar values, this is not a valid value in
> SQL. This violates a thing called First Normal Form (1NF) which you

I think you are confusing the query language (SQL, or in this product TSQL)
with database design.

In database design yes, you would eradicate this type of structure.

SQL is querying - nothing more.

You also show a sheltered existence, really, have you never had to chop and
refine data - would you honestly write a separate one off program in C when
in a fraction of the time you could cut and massage the data into a
structure you want and conforms to proper database design principles?

--ROGGIE--

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:6ab35ce4-1acc-48cb-8c6e-08bb960ccc6a(a)c34g2000yqn.googlegroups.com...
>>> I have a field [sic: columns are not fields] that contains values like
>>> "1,3,0,0" and "2,0,0" and "1". I need to sum those values within the
>>> field [sic]. The field [sic] can have up to 100 numbers separated by
>>> commas. How can I do sum them? <<
>
> BY DEFINITION a column has scalar values, this is not a valid value in
> SQL. This violates a thing called First Normal Form (1NF) which you
> will find in the first few chapters of a book on RDBMS.
>
> You can find lots of stinking kludges to parse strings, but the right
> answer is to design a proper schema and fire the guy who did this.
>
> If you use a kludge, be sure to include code to raise all the errors
> that a parameter in a procedure call can raise. The cowboy coders
> never bother with that part ..

From: --CELKO-- on 23 Dec 2009 18:15
>> SQL is querying - nothing more. <<

Actually it is DDL, DML, DCL and transaction control. This is one of
the major problems that cowboy coders have; they think SQL = DML, and
fail to create integrated systems.

>> You also show a sheltered existence, really, have you never had to chop and refine data - would you honestly write a separate one off program in C when in a fraction of the time you could cut and massage the data into a structure you want and conforms to proper database design principles? <<

LOL! These days, I use a word processor or a spreadsheet most of the
time! I find that most one-shot jobs have the data I want on a
website for download. Most of the cleanup work is with text --
squeeze spaces, change case, run a macro and stick it into a CSV file
for BCP or some INSERT INTO statements.

For repeated jobs, we usually have an ETL tool in my "sheltered
existence" IT shops.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Connection string to SQL Server to 2005
Next: tsql to change oracle pswd