How can I cast a query result to string and insert it into a table row

Question

I'm getting an error that the subquery returns more than one value and I know it does so now I would like to cast that subquery result to a string and then insert that string into the table on the same position

I've already tried with a loop but since I can't use loops anywhere else than outside the query it didn't help much.

That is the query I'm working with

SELECT DISTINCT LiveCampaign_SubscriberList_Email.Email, Firstname, Lastname, dbo.spValueToString(Subscribed) AS Subscribed,

dbo.spValueToString((SELECT COUNT(*) 
    FROM tblEmailBlackList
    WHERE tblEmailBlackList.Email = LiveCampaign_SubscriberList_Email.Email and tblEmailBlackList.PortalID = -2)) As GeneralBlockList,

(SELECT DISTINCT PortalLocalization.PortalName
    FROM tblEmailBlackList
    LEFT JOIN tblLiveCampaignSettings ON tblLiveCampaignSettings.ModuleID = tblEmailBlackList.ModuleID
    LEFT JOIN PortalLocalization ON PortalLocalization.PortalID = tblEmailBlackList.PortalID AND PortalLocalization.CultureCode = 'sl-SI'
    LEFT JOIN #tmp ON tblEmailBlackList.Email = #tmp.Email COLLATE Slovenian_CI_AS
    WHERE LiveCampaign_SubscriberList_Email.Email = #tmp.Email COLLATE Slovenian_CI_AS
    AND tblEmailBlackList.PortalID >= 0 OR tblEmailBlackList.PortalID =- 1)
    AS LocalBlockList

FROM LiveCampaign_SubscriberList_Email

And this is the part I need to be casted into a string:

(SELECT DISTINCT PortalLocalization.PortalName
    FROM tblEmailBlackList
    LEFT JOIN tblLiveCampaignSettings ON tblLiveCampaignSettings.ModuleID = tblEmailBlackList.ModuleID
    LEFT JOIN PortalLocalization ON PortalLocalization.PortalID = tblEmailBlackList.PortalID AND PortalLocalization.CultureCode = 'sl-SI'
    LEFT JOIN #tmp ON tblEmailBlackList.Email = #tmp.Email COLLATE Slovenian_CI_AS
    WHERE LiveCampaign_SubscriberList_Email.Email = #tmp.Email COLLATE Slovenian_CI_AS
    AND tblEmailBlackList.PortalID >= 0 OR tblEmailBlackList.PortalID =- 1)

Result would be a string which is then inserted into the table in the same row as the result of this subquery should be

Then you need to give us logic by which we can limit this query to returning only a single row. — Tim Biegeleisen, Apr 05 '19 at 07:00
So you want to aggregate all `PortalName` returned from the query you gave, separated by something (so say if it returns two rows - 'A' and 'B', you want it to be 'A, B'?) and then insert it where? — MarcinJ, Apr 05 '19 at 07:09
@MarcinJ yeah there can be more than one PortalName returned and i want all to be in one row and the insert would be at the same place as the result of the subquery would be. I'm gonna add a bit more code to that — hyper, Apr 05 '19 at 07:11
which SQL Server version? Could you please add sql-server- tag? — MarcinJ, Apr 05 '19 at 07:32
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — MarcinJ, Apr 05 '19 at 07:44
Tried that STUFF function but I still get that the subquery returned more than one value @MarcinJ — hyper, Apr 05 '19 at 07:53

score 0 · Accepted Answer · answered Apr 05 '19 at 07:57

So you need to aggregate the returned rows into a single one. Since you're on SQL Server 2014, STRING_AGG is not available, so we will use the XML aggregation trick. Try this:

SELECT DISTINCT LiveCampaign_SubscriberList_Email.Email, Firstname, Lastname, dbo.spValueToString(Subscribed) AS Subscribed,

dbo.spValueToString((SELECT COUNT(*) 
    FROM tblEmailBlackList
    WHERE tblEmailBlackList.Email = LiveCampaign_SubscriberList_Email.Email and tblEmailBlackList.PortalID = -2)) As GeneralBlockList,
STUFF((
    SELECT DISTINCT ',' + PortalLocalization.PortalName
    FROM tblEmailBlackList
    LEFT JOIN tblLiveCampaignSettings ON tblLiveCampaignSettings.ModuleID = tblEmailBlackList.ModuleID
    LEFT JOIN PortalLocalization ON PortalLocalization.PortalID = tblEmailBlackList.PortalID AND PortalLocalization.CultureCode = 'sl-SI'
    LEFT JOIN #tmp ON tblEmailBlackList.Email = #tmp.Email COLLATE Slovenian_CI_AS
    WHERE LiveCampaign_SubscriberList_Email.Email = #tmp.Email COLLATE Slovenian_CI_AS
    AND tblEmailBlackList.PortalID >= 0 OR tblEmailBlackList.PortalID =- 1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    AS LocalBlockList

FROM LiveCampaign_SubscriberList_Email

That solves it thanks! Also just noticed that I will have to use UNION and I think that won't work with FOR XML but I will try to work around it — hyper, Apr 05 '19 at 08:05

How can I cast a query result to string and insert it into a table row

1 Answers1