I need something similar to "Sum()" but for strings. 3

[holdemfoldem]

To get the sum of a given column for a group of rows sharing the same column value, you do the following:

SELECT ccustno,
sum(nbalance) as totalbalance
FROM arinvc
GROUP BY ccustno

My question: How do you concatenate the string values of a given column for a group of rows? I know you can concantenate strings with the "+" or "||" operator, but this works only for different columns of the same row and is not what I need.
In my case, I have several rows for the same item in an inventory table, say a Compaq PC Model 12345, each of which contains a description column. The first row may contain "128 meg Ram" in the description column, the next row may contain "40x CD", etc, and I need to combine these into a single description field to go with single row for that computer, resulting in something like:
Compaq PC Model 12345 128 meg Ram, 40x CD, etc
Thanks ahead for help!

 

[JNC73]

Hi HoldemFoldem,

What you need to do is build a Loop in SQL Server

This is a litte with PSEUDO CODE SO YOU have to change it into real SQL code

DECLARE @PCDESC VARCHAR(255)
DECLARE @COUNTER INTEGER

SELECT @PCDESC = ""
SELECT @COUNTER = (SELECT MIN(TABLE_ID) FROM TABLE)

WHILE @@ROWCOUNT = 1
BEGIN
SELECT @PCDESC = @PCDESC + COLUMNNAME FROM TABLE
WHERE Table_id = @COUNTER

SELECT @COUNTER = @COUNTER + 1
END

Hope this helps

JNC73

 

[agar]

You can also use a cursor, in case the ID's aren't contiguous. Here's an example (this is all in TSQL):

-----------------------------------------
declare @t varchar(...)
declare @tdesc varchar(...)

declare tcursor
 cursor local for
  select description
   from inventory
   where ...
   order by ...

set @tdesc = ''
open tcursor
fetch next from tcursor
 into @t
while (@@fetch_status = 0)
 begin
	set @tdesc = @tdesc + ', ' + @t
	fetch next from tcursor
	 into @t
 end
close tcursor
deallocate tcursor

-- do something w/ @tdesc
go
--------------------------------------

This query is quite a bit longer than the one you wanted to write. If you are just writing the query into a string variable, passing it to your database driver, which then forwards it for you to the database, you might want to consider creating stored procedures instead, which would encapsulate the above logic. The other advantage to using stored procedures is that these complex queries can be precompiled, which means that the DB creates an execution plan for it once and then reuses that plan. Otherwise I'm fairly sure that the DB has to recreate the same execution plan every time you send it the string.

Creating SP's is very easy, you can do it either in SQL Server Enterprise Manager or (what I use) SQL Server Query Analyzer. If you create a stored procedure, you just give it name (like a function) and just send this to your driver:

"exec mystoredprocedure arg1, 'arg2', arg3"

You can find more info in the documentation.

 

[foxdev]

Remembering back to a thread a few months ago on how to have "running tallies" stored in a memvar, I came up with how to do this without cursors.

[tt]
declare @String varchar(150)

select @String = ''

select @String = @String + MyColumn
from MyTable

select @String[/tt]
Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[agar]

Yes you can do it without cursors, but it's a little more complicated than that. You still have to have a loop, and you have to have a primary key column in the table, which will give each row a unique ID. The ID's don't have to be integers and they don't have to be contiguous, but they do need to be distinct, which they will be if they are primary keys.

declare @string varchar(...)
declare @IDvar int -- or whatever datatype, datetime, etc
select @IDvar = min(ID)
 from TABLE
 where ...condition...
while (@IDvar IS NOT NULL)
 begin
      select @string = @string + description
       from TABLE
       where ID = @IDvar
        and ...condition...
      select @IDvar = min(ID)
       from TABLE
       where ID > @IDvar
        and ...condition...
 end

I have tested this against using a cursor, and it seems slower. However it probably also depends on what type of cursor you are using, etc. Also note that "...condition..." needs to be the same in all three queries.

 

[foxdev]

Agar, its not more complicated - the example I gave above is only four statements. All holdemfoldem needs to add is the WHERE clause to the SELECT statement to limit the rows processed. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[holdemfoldem]

First, let me thank one and all for your replies! This is my first post to this board, and I am very impressed with the eagerness and technical expertise expressed.
Second, let me applogize if I posted to the wrong board. I don't think I did, but let me explain a bit further:
I am currently working within the confines of an in house application that processes tables through a filter via SQL statements and creates various temporary cursors, and, eventually, new permanent tables, formatted as the need requires. It can process a variety of tables from DBase to FoxPro to Paradox to ODBC, using whatever engine drives the given data base format. We build the cursors and/or tables using SQL statements to pull data from existing tables or cusors.
Although I suppose a function of some sort could be written as an add on to this application (and indeed may have to), which I could then call from my SQL statements, in its current form, I am constrained to typing straight SQL statements, pulling data from one table or cursor into another (whatever cursor name I indicate in a separate field from the SQL statement).
Although I admit I'm more than fuzzy distinguishing the difference between the writing of SQL statements and writing code which can be compiled in, say MS SQL Server, Oracle, etc (which I've never done), I have been writing code in various languages for almost 20 years and I'm sure I could do so, if necessary, (although all the hand holding and exact syntax you can supply for such a function would be greately appreciated, if it comes down to this!). I currently have MS SQL Server 6.5 and MS Visual Studio 6.0 installed on my machine, if these can be used for such an endeavor.
That having been said, if there's a way, using SQL statements, to produce a cursor from an existing inventory table with columns such as:
Compaq Model 12345 128 Meg, 40X CD, 17 in monitor, etc
IBM Model 54321 64 Meg, 32X CD, Vodoo Sound Card, etc
etc...
from a table containing multiple entries for each item name (item name unique), that would be great.
I guess it shouldn't bug me as much as it does, but it just seems like there should be a way to do something similar for string concatenation as the following, which would get me the tallied price of each part if I bought them separately, illustrating the value of purchasing them as a unit:
SELECT ItemName,
sum(IndvPrice) as TalliedPrice
FROM Inventory
WHERE not void
GROUP BY ItemName
Which would result in:
Compaq Model 12345 2,482.34
IBM Model 54321 1,764.83
etc...
I've used the above many times, but I guess SQL just doesn't provide something similar for strings.

Again, let me thank one and all for your eager and, obviously, technically adept, replies! Your help really is appreciated.

 

[holdemfoldem]

Whoops! I said, "item name unique" in my last post. Obviously, this is NOT the case, since the whole point is to group the rows with the same item name! My bad! :^\

 

[foxdev]

HF, I think if you use the technique I've described, then include the result (@String, in my example) in an INSERT INTO #MYTABLE, it might come close to what you want. Its not a "straight into a cursor" method, but as close as I can imagine.

So, rather than what you use for numbers:

[tt]SELECT ItemName,
sum(IndvPrice) as TalliedPrice
FROM Inventory
WHERE not void
GROUP BY ItemName [/tt]

you'd have something like:

[tt]declare @String varchar(150)
select @String = ''
select @String = @String + MyColumn
from MyTable
where ProductName = 'whatever'

insert into #MyTempTable (ProductSpec)
values (@String)[/tt]

The big downside, and the part that I haven't accounted for, is that you'd have to run this for each distinct ProductName, since there is no GROUP BY. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[agar]

foxdev, wow, you're totally right. I didn't believe you at first but I just tried it and it works. I guess that's how you got to be a "Sr. DBA" at "some big company" I'll just shut up now.

BTW, since you're so smart -- can you answer this one for me (I've been stuck on this for over a day and I want to move on): why do I keep getting deadlocks when the concurrent batches that are deadlocking should only be contending for one object to begin with (a single row in a single table)? I thought a deadlock happened only when process A has a lock on Obj.1 and wants a lock on Obj.2, and process B has a lock on Obj.2 and wants a lock on Obj.1? Does it have something to do with system tables or indexes? These deadlocks should not be happening.

 

[foxdev]

The key is "should be contending for one object"; it could be that there is more going on than you might logically expect looking at the statements.

Without knowing more about the processes and tables, its difficult to come up with a blanket "likely culprit".

If you haven't already, start another thread on it and let us hack at it.

And I can't take credit for the [tt]select @String = @String + MyColumn [/tt] trick; someone else on the forum came up with that a few months ago regarding a different problem; I merely adapted it.

I'm not bright at all; just persistent. And I've been around forever (read: I've made lots of mistakes to learn from). Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[agar]

Thanks very much... I started a thread last night, then I figured it out anyway on the way home. Basically I had two transactions selecting from a row and getting shared locks on it, then both trying to update the row and deadlocking trying to get the exclusive lock for the update. I used the "UPDLOCK" table hint to solve it, it seems to work now. But thanks for your help, and again, sorry about getting in your face about the string concat thing :-(

 

[dwilson1976]

 

[TomasDill]

Hi!

Looked at this thread and see there are a lot of solutions here for this particular case. There is another one in the FAQ "How to organize SUM() for character field in SQL Server SELECT query". If you have a time, take a look at it and tell me your opinion.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[holdemfoldem]

TomasDill,

Thanks! Now - this is really embarassing! How do you access the FAQ? I can't find any links for it, just links for forums. I have tried typing in, "How to organize SUM() for character field in SQL Server SELECT query", "sum()", and "faq" in their search field, but still can't find it!
TAH!

holdemfoldem

 

[tlbroadbent]

You could go to the FAQ page and do a find on the page. Use Ctrl-F in IE. The FAQ in question is faq183-1067. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.