4/19/2024 3:36:13 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Concatenate Rows from SQL UNION results.
Posted: 15 Jun 10 2:28 PM
|
I am trying to concatenate the results from the following UNION. This will only ever produce 0, 1 or 2 emails and need to be comma delimited. Is there any way to do it on the fly? This is being fed to a KnowledgeSync subscriber event.
SELECT sysdba.USERINFO.EMAIL
FROM sysdba.ACCOUNT INNER JOIN
sysdba.USERSECURITY ON sysdba.ACCOUNT.ACCOUNTMANAGERID = sysdba.USERSECURITY.USERID INNER JOIN
sysdba.USERINFO ON sysdba.USERSECURITY.MANAGERID = sysdba.USERINFO.USERID INNER JOIN
sysdba.USERPROFILE AS USERPROFILE_1 ON sysdba.USERSECURITY.MANAGERID = USERPROFILE_1.USERID
WHERE (sysdba.ACCOUNT.ACCOUNTID = 'SOMEACCOUNT') AND (USERPROFILE_1.USERTEXT1 LIKE '%A%')
UNION
SELECT USERINFO_1.EMAIL
FROM sysdba.ACCOUNT AS ACCOUNT_1 INNER JOIN
sysdba.USERINFO AS USERINFO_1 ON ACCOUNT_1.ACCOUNTMANAGERID = USERINFO_1.USERID INNER JOIN
sysdba.USERPROFILE AS USERPROFILE_1 ON ACCOUNT_1.ACCOUNTMANAGERID = USERPROFILE_1.USERID
WHERE (ACCOUNT_1.ACCOUNTID = 'SOMEACCOUNT') AND (USERPROFILE_1.USERTEXT1 LIKE '%A%')
Thanks ahead of time.
Michael.
|
|
|
|
Re: Concatenate Rows from SQL UNION results.
Posted: 15 Jun 10 3:35 PM
|
For this I typically use a User Defined Function, and include it on my Datasource:
e.g. SELECT TICKETID, STATUS, GetRecipientsEmail() FROM TICKET WHERE STATUS = 'XYZ'
The GetRecipientsEmail is an UDF that returns a string. It Opens a cursor and parses the result of the Query.
e.g.
function GetRecipientsEmail() returns varchar(5000)
AS
begin
DECLARE @email varchar(128)
DECLARE @emails varchar(5000)
DECLARE tEmail CURSOR FOR
SELECT sysdba.USERINFO.EMAIL
FROM sysdba.ACCOUNT INNER JOIN
sysdba.USERSECURITY ON sysdba.ACCOUNT.ACCOUNTMANAGERID = sysdba.USERSECURITY.USERID INNER JOIN
sysdba.USERINFO ON sysdba.USERSECURITY.MANAGERID = sysdba.USERINFO.USERID INNER JOIN
sysdba.USERPROFILE AS USERPROFILE_1 ON sysdba.USERSECURITY.MANAGERID = USERPROFILE_1.USERID
WHERE (sysdba.ACCOUNT.ACCOUNTID = 'SOMEACCOUNT') AND (USERPROFILE_1.USERTEXT1 LIKE '%A%')
UNION
SELECT USERINFO_1.EMAIL
FROM sysdba.ACCOUNT AS ACCOUNT_1 INNER JOIN
sysdba.USERINFO AS USERINFO_1 ON ACCOUNT_1.ACCOUNTMANAGERID = USERINFO_1.USERID INNER JOIN
sysdba.USERPROFILE AS USERPROFILE_1 ON ACCOUNT_1.ACCOUNTMANAGERID = USERPROFILE_1.USERID
WHERE (ACCOUNT_1.ACCOUNTID = 'SOMEACCOUNT') AND (USERPROFILE_1.USERTEXT1 LIKE '%A%')
OPEN tEmail
FETCH NEXT from tEmail INTO @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @email <> ''
BEGIN
set @emails = isnull(@emails, '') + @email + ', '
END
FETCH NEXT from tEmail INTO @email
END -- while
CLOSE tEmail
DEALLOCATE tEmail
return Left(@emails, Len(@emails) - 1)
end
|
|
|
|
Re: Concatenate Rows from SQL UNION results.
Posted: 15 Jun 10 4:25 PM
|
If you're prepared to enter the world of CTEs and XML PATHs, there's a pretty quick way.
Take the following example UNION:
select '1' id union select '2' union select '3'
execute it and you obtain
1
2
3
But if you wrap it in a few bits of fancy SQL (2005 and later):
with q as
(select '1' id union select '2' union select '3')
SELECT
STUFF(
(
SELECT
',' + ID
FROM q
FOR XML PATH('')
), 1, 1, ''
) As concatenated_string
You'll get 1,2,3 as required. |
|
|
|
Re: Concatenate Rows from SQL UNION results.
Posted: 17 Jun 10 8:29 AM
|
Worked like a charm Raul. Thank you.
The only thing I added was passing the AccountID variable which I had listed as "SOMEACCOUNT".
function GetRecipientsEmail(@accountid VARCHAR(12))
Thanks again! |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|