Вопросы Теги

SQL-Как разделить несколько элементов от 1 строки до нескольких строк

Для java 7 и выше: 

Path path = Paths.get("/your/path/string");
if(!Files.exists(path)) {
    try {
      Files.createDirectories(path);
    } catch (IOException e) {
      e.printStackTrace();
    }
}
0
задан Samayoa 16 January 2019 в 23:51
поделиться

1 ответ

Здесь я внес эти изменения, основываясь на предоставленных вами данных. 

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 Email1@email.com','47213','Email1@email.com' UNION ALL
Select 'Report A','Email sent to Email100@email.com','13983','Email100@email.com' UNION ALL
Select 'Report A','Email sent to Email101@email.com','437707','Email101@email.com' UNION ALL
Select 'Report B','Email sent to Email103@email.com','NULL','Email103@email.com' UNION ALL
Select 'Report C','Email sent to Email110@email.com','NULL','Email110@email.com' UNION ALL
Select 'Report C','Email sent to Email128@email.com','NULL','Email128@email.com' UNION ALL
Select 'Report C','Email sent to Email2@email.com;Email3@email.com','170891','Email2@email.com;Email3@email.com' UNION ALL
Select 'Report D','Done: 1 processed of 1 total; 0 errors.','NULL','Email200@email.com;Email5000@email.com;Email1000@email.com;Email_001@email.com'
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

Результат: 

Name    email
Report A    Email1@email.com
Report A    Email100@email.com
Report A    Email101@email.com
Report B    Email103@email.com
Report C    Email110@email.com
Report C    Email128@email.com
Report C    Email2@email.com
Report C    Email3@email.com
Report D    Email200@email.com
Report D    Email5000@email.com
Report D    Email1000@email.com
Report D    Email_001@email.com
0
ответ дан Alen.Toma 16 January 2019 в 23:51
поделиться
Другие вопросы по тегам:

Похожие вопросы: