SUM String - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SUM String

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-18 : 08:10:14

Hi Reader,

I have the below

SELECT	SalesPerson = (SELECT ContactName FROM vw__CalendarSalesPerson)

FROM	tblCalendar

I want to add together all the strings in this sub query, is it possible. I have had a look around and cannot find anything about it.

Thanks

Leah

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-18 : 08:29:52

DECLARE @MyString VARCHAR (8000)
SET @MyString = ''

SELECT @MyString = @MyString + ColumnofVarchar
FROM MyTable

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 08:49:23

SELECT SalesPerson = (SELECT ContactName FROM vw__CalendarSalesPerson)
FROM tblCalendar

?????? This doesn't make sense. What if there are multiple ContactName in the view? You haven't done anything to limit it. Can you give an example with some data of what you're trying to accomplish.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-18 : 10:17:57

(SELECT ContactName FROM vw__CalendarSalesPerson) will have results such as:

LS
JM
GB
AB
RS

what I want SalesPerson to contain is all those values added together so SalesPerson would have LS, JM, GB, AB, RS,
so I guess when I select the contact name I want to do ContactName + ', '

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-18 : 10:19:16

Is this possible, I am thinking of maybe making it a function that will use a cursor to add the contact names together but it seems a bit fidly doing it that way

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-18 : 15:37:53

The query I posted above will concatenate the names the way you requested. If you want to separate them with a comma, here's a way to do that:

DECLARE @MyString VARCHAR (8000)
SET @MyString = NULL

SELECT @MyString = ISNULL(@MyString + ',', '') + ColumnofVarchar
FROM MyTable

-- If you want to return the value in a recordset, follow the above with this line:

SELECT @MyString

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-21 : 04:12:25

I have tried using your code by it keeps saying:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The code works fine on its own, its when I use it with other fields as well.

SELECT EventID,
EventStart,
EventFinish,
SalesPerson = (SELECT ContactFirstName FROM vw__CalendarSalesPerson)

FROM tblCalendar

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-21 : 09:20:02

quote:
Originally posted by leahsmart
I have tried using your code by it keeps saying:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I could point to the problem if you would post your query that returned this error/

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-21 : 09:21:37

I have entered the query above

SELECT EventID,
EventStart,
EventFinish,
SalesPerson = (SELECT ContactFirstName FROM vw__CalendarSalesPerson)

FROM tblCalendar

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-21 : 09:23:33

DECLARE @MyString VARCHAR (8000
SET @MyString = NULL
SELECT @MyString = IsNull(@MyString + ',', '') + ContactName As SalesPersons
FROM vw__CalendarSalesPerson

-- Return the value as a recordset.
SELECT @MyString

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-21 : 09:39:30

You are missing the point, this is one field in the record set

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-21 : 09:46:16

An the number one question asked at sql team:

http://www.sqlteam.com/item.asp?ItemID=2368

Brett

8-)

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-21 : 09:49:55

Yes but I want it to appear as one of the fields in my query not a variable, each line is dependant on the event id

SELECT EventID,
EventStart,
EventFinish,
SalesPerson = (SELECT ContactFirstName FROM vw__CalendarSalesPerson where CalendarSalesPerson.eventid = tblCalendar.eventid)

FROM tblCalendar

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-21 : 10:12:35

Maybe there's a 2nd step to get where you want to be...

SELECT T.EventID, T.EventStart, T.EventFinish, P.ContactFirstname
  FROM tblCalendar T
  INNER JOIN vw_CalendarSalesPerson P
    ON P.EventID = T.EventID

Now you want to collapse all rows of the same EventID and concatenate all the ContactFirst names into a single comma(?) delimited string?

This is another kettle of fish.

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-21 : 10:14:32

quote:
Originally posted by X002548

An the number one question asked at sql team:

http://www.sqlteam.com/item.asp?ItemID=2368 height="1" noshade id="quote">

The problem with this solution is that it works for a single element. I don't know of any way to nest this into a recordset of many EventIDs - like in the last query I posted above.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-21 : 10:17:15

What I have done at the moment is to call a function that runs the code you gave me and returns a string for each row. This seems to be working ok but is probably not the most eligant way of doing it.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-21 : 13:53:27

Actually, it probably is the most elegent way to do it. The only other way to do it is to loop through each row and create a concatenated value for each row. The function encapsulates it and is a perfect use of a function.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources