SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SUM String
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/18/2004 :  08:10:14  Show Profile  Reply with Quote
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

USA
3467 Posts

Posted - 06/18/2004 :  08:29:52  Show Profile  Reply with Quote
DECLARE @MyString VARCHAR (8000)
SET @MyString = ''

SELECT @MyString = @MyString + ColumnofVarchar
FROM MyTable
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/18/2004 :  08:49:23  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/18/2004 :  10:17:57  Show Profile  Reply with Quote
(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 + ', '
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/18/2004 :  10:19:16  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/18/2004 :  15:37:53  Show Profile  Reply with Quote
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
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/21/2004 :  04:12:25  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/21/2004 :  09:20:02  Show Profile  Reply with Quote
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/
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/21/2004 :  09:21:37  Show Profile  Reply with Quote
I have entered the query above

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

FROM tblCalendar
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/21/2004 :  09:23:33  Show Profile  Reply with Quote
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
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/21/2004 :  09:39:30  Show Profile  Reply with Quote
You are missing the point, this is one field in the record set
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/21/2004 :  09:46:16  Show Profile  Reply with Quote
An the number one question asked at sql team:

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



Brett

8-)
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/21/2004 :  09:49:55  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/21/2004 :  10:12:35  Show Profile  Reply with Quote
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.
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/21/2004 :  10:14:32  Show Profile  Reply with Quote
quote:
Originally posted by X002548

An the number one question asked at sql team:

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

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.
Go to Top of Page

leahsmart
Posting Yak Master

United Kingdom
133 Posts

Posted - 06/21/2004 :  10:17:15  Show Profile  Reply with Quote
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.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/21/2004 :  13:53:27  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000