| Author |
 Topic  |
|
|
leahsmart
Posting Yak Master
United Kingdom
133 Posts |
 Posted - 06/18/2004 : 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
USA
3459 Posts |
Posted - 06/18/2004 : 08:29:52
|
DECLARE @MyString VARCHAR (8000)
SET @MyString = ''
SELECT @MyString = @MyString + ColumnofVarchar
FROM MyTable |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/18/2004 : 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
United Kingdom
133 Posts |
Posted - 06/18/2004 : 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
United Kingdom
133 Posts |
Posted - 06/18/2004 : 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
USA
3459 Posts |
Posted - 06/18/2004 : 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
United Kingdom
133 Posts |
Posted - 06/21/2004 : 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
USA
3459 Posts |
Posted - 06/21/2004 : 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
United Kingdom
133 Posts |
Posted - 06/21/2004 : 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
USA
3459 Posts |
Posted - 06/21/2004 : 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
United Kingdom
133 Posts |
Posted - 06/21/2004 : 09:39:30
|
| You are missing the point, this is one field in the record set |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
leahsmart
Posting Yak Master
United Kingdom
133 Posts |
Posted - 06/21/2004 : 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
USA
3459 Posts |
Posted - 06/21/2004 : 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
USA
3459 Posts |
Posted - 06/21/2004 : 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
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
United Kingdom
133 Posts |
Posted - 06/21/2004 : 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
USA
4184 Posts |
Posted - 06/21/2004 : 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. |
|
|
| |
Topic |
|
SUM String
