summing varchar values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

summing varchar values

Author

Topic

alecwh22
Starting Member

15 Posts

Posted - 2012-03-20 : 06:02:59

I frequently have the problem that a nested statement will return multiple values such as:

select supplier_item_id from stock_lines

-->

supplier_item_id
----------------
258134
8370183
4920894

and I want to do a "varchar sum" that would work something like

select VARCHARSUM(select supplier_item_id from stock_lines) as supplier_item_id_together

-->

supplier_item_id_together
-------------------------
258134,8370183,4920894

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-20 : 06:52:40

If you are on SQL 2005 or higher, you can use XML PATH like this:

SELECT
	','+CAST(supplier_item_id AS VARCHAR(32)) AS [text()]
FROM
	YourTable
FOR XML PATH('');

That will get you the comma-separated string, but with a comma as the first character. You can strip of that comma, for example, using the STUFF function:

SELECT STUFF((
SELECT
	','+CAST(supplier_item_id AS VARCHAR(32)) AS [text()]
FROM
	YourTable
FOR XML PATH('')),1,1,'');

alecwh22
Starting Member

15 Posts

Posted - 2012-03-20 : 07:08:30

can you explain more about the XML PATH? (I'm not sure of its syntax) and this seems much better than the work around that was not working for me:

go
begin try
drop function dbo.VARCHARSUM
end try
begin catch
end catch

go
create function dbo.VARCHARSUM (@list_query varchar(max))
returns varchar(max)
begin

declare @list varchar(max)

declare @list_table table(idx smallint Primary Key IDENTITY(1,1),list_col varchar(max))

exec('insert into #list_table (list_col) ' + @list_query)

declare @i int
set @i = 1
declare @last_index int
set @last_index = (select case when MAX(idx) is null then 0 else MAX(idx) end from @list_table)

if @last_index > 0
begin

while(@i <= @last_index)
begin
set @list = @list + ',' + (select list_col from @list_table where idx = @i)
set @i = @i + 1
end

declare @length int
set @length = LEN(@list) - 1

set @list = substring(@list,1,@length)

end
else
begin
set @list = 'no arguments returned'
end

return(@list)

end

it turns out you cant use dynamic sql in a user defined function so i'm not sure how to get the multiple values in to @list_table

but yours seems better any way

alecwh22
Starting Member

15 Posts

Posted - 2012-03-20 : 07:09:10

oh i'm on sql server 2008 r2

alecwh22
Starting Member

15 Posts

Posted - 2012-03-20 : 07:18:40

your solution is brilliant but I made the following changes

declare @list varchar(max)
set @list =
(
SELECT
','+CAST(supplier_item_id AS VARCHAR(32)) AS [text()]
FROM
stock.dbo.stock_levels
FOR XML PATH('')
)

set @list = SUBSTRING(@list,2,Len(@list)-1)

select @list as list

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-20 : 07:30:29

quote:
Originally posted by alecwh22

declare @list varchar(max)
set @list =
(
SELECT
','+CAST(supplier_item_id AS VARCHAR(32)) AS [text()]
FROM
stock.dbo.stock_levels
FOR XML PATH('')
)

set @list = SUBSTRING(@list,2,Len(@list)-1)

select @list as list

Looks good to me too! Glad you got it worked out.

quote:
can you explain more about the XML PATH?

XML PATH is part of the XML implementation in SQL. XML PATH('Node') specifies that you want to create XML with a wrapper node named "Node". When you use XML PATH('') it omits any wrapper elements and ends up with what you saw. (Not a very good explanation, is it?

). Take a look at this page, may be that will help: http://msdn.microsoft.com/en-us/library/ms173812.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources