SQL-How to split multiple items from 1 row to multiple rows

Question

I'm working on creating a report that will tell me how many emails are assigned to a report subscription , for example a report called "Monthly Customer Activity - Default Setup" can have 2 email subscriptions and each subscription can have different email addresses , lets say 5 emails each subscription , at the end my report will display on one column 10 times the report Name and on the other column the 10 email addresses.

I'm working using the code below and is working most of the time but on some rows is not doing the email split . I'm using SQL Management Studio 2016 and I'm connecting to my SSRS 2016 Database

Use [ReportServer]
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
drop table #Temp

select
c.Name,
-- 'two' as OtherID,
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(max)') as email
INTO #Temp
FROM 
 dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

;
WITH tmp(/*SomeID, OtherID,*/ Name, email,string) AS
(
    SELECT
        Name,
        --OtherID,
        LEFT(email, CHARINDEX(';', email + ';') - 1),
        STUFF(email, 1, CHARINDEX(';', email + ';'), '')
    FROM  #Temp

    UNION all

    SELECT
        Name,
        --OtherID,
        LEFT(email, CHARINDEX(';', email + ';') - 1),
        STUFF(email, 1, CHARINDEX(';', email + ';'), '')
    FROM tmp
    WHERE
        email != email
      and email is not NULL
)


select * from #Temp
order by email

The desired results will be: https://imggmi.com/full/2019/1/17/a528cf472a96f5d2eff2759413b79814-full.png.html

Results I'm getting:

https://imggmi.com/full/2019/1/17/4ae835b3a877ad015af10372cf7f82e9-full.png.html

As you can see on the picture , is doing the split email on some rows but some others are still showing the emails together

Below is a sample test:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL drop table #Temp
CREATE TABLE #Temp
(Name varchar (50) , LastStatus varchar (max) , IBOAccount varchar (10) , Email varchar (max))
GO
INSERT INTO #Temp
Select 'Report A','Email sent to [email protected]','47213','[email protected]' UNION ALL
Select 'Report A','Email sent to [email protected]','13983','[email protected]' UNION ALL
Select 'Report A','Email sent to [email protected]','437707','[email protected]' UNION ALL
Select 'Report B','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected];[email protected]','170891','[email protected];[email protected]' UNION ALL
Select 'Report D','Done: 1 processed of 1 total; 0 errors.','NULL','[email protected];[email protected];[email protected];[email protected]'
GO

Since you are on 2016, I would suggest looking into string_split(). Or perhaps supply a small data sample and desired results — John Cappelletti
– John Cappelletti, Commented Jan 16, 2019 at 21:32
John , the Database server is 2008 , only the management studio is 2016 . I've tried to use that function and I've got ""Invalid object name 'STRING_SPLIT'."" — Samayoa
– Samayoa, Commented Jan 16, 2019 at 22:25

Alen.Toma · Accepted Answer · 2019-01-17 00:02:42Z

1

here i made those changes based on your data you provided

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL drop table #Temp
CREATE TABLE #Temp
(Name varchar (50) , LastStatus varchar (max) , IBOAccount varchar (10) , Email varchar (max))
GO
INSERT INTO #Temp
Select 'Report A','Email sent to [email protected]','47213','[email protected]' UNION ALL
Select 'Report A','Email sent to [email protected]','13983','[email protected]' UNION ALL
Select 'Report A','Email sent to [email protected]','437707','[email protected]' UNION ALL
Select 'Report B','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected]','NULL','[email protected]' UNION ALL
Select 'Report C','Email sent to [email protected];[email protected]','170891','[email protected];[email protected]' UNION ALL
Select 'Report D','Done: 1 processed of 1 total; 0 errors.','NULL','[email protected];[email protected];[email protected];[email protected]'
GO;

declare  @result table (Name  nvarchar(max), email  varchar(MAX) )
while (select count(*) from #Temp)>0 
 begin
 declare @email varchar(max) = (select top 1 email from #temp)
 declare @Name varchar(max) = (select top 1 Name from #Temp)
 delete top (1) from #Temp where Name = @Name;
  IF RIGHT(@email, 1) <> ';'
    SELECT @email = @email + ';'

    DECLARE @Pos    BIGINT,
            @OldPos BIGINT
    SELECT  @Pos    = 1,
            @OldPos = 1

    WHILE   @Pos < LEN(@email)
        BEGIN
            SELECT  @Pos = CHARINDEX(';', @email, @OldPos)
            INSERT INTO @result (name , email)
            SELECT @Name, LTRIM(RTRIM(SUBSTRING(@email, @OldPos, @Pos - @OldPos))) email

            SELECT  @OldPos = @Pos + 1
        END

 end 

select * from @result

result:

Name    email
Report A    [email protected]
Report A    [email protected]
Report A    [email protected]
Report B    [email protected]
Report C    [email protected]
Report C    [email protected]
Report C    [email protected]
Report C    [email protected]
Report D    [email protected]
Report D    [email protected]
Report D    [email protected]
Report D    [email protected]

edited Jan 17, 2019 at 0:02

answered Jan 16, 2019 at 22:58

Alen.Toma

4,8782 gold badges19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samayoa Over a year ago

Ale , I've added a sample test data based on the real data

Samayoa Over a year ago

Thank you , I'm still trying to figure out how to add the other 2 columns to the final results but your script does what I needed . Thank you so much and awesome code :)

Collectives™ on Stack Overflow

SQL-How to split multiple items from 1 row to multiple rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related