How to append a list to a list without repeats?

Question

SQL Server

The music album for performers has a list of instruments (roles), for example:

Paul McCartney : lead vocals, guitar, bass, piano, hammond organ, drums, timpani, percussion, recorder, flugelhorn
Mal Evans: backing vocals (2, 6), saxophone (23)

How to append a list to a list without repeats? For example:

Mal Evans: backing vocals (2), handclaps (2, 18), trumpet (23)

Answer 1

CREATE TRIGGER AlbumRoleInsert ON AlbumRole INSTEAD OF INSERT AS 
BEGIN
  SET NOCOUNT ON
  DECLARE @ALB INT, @PRF INT, @ROL VARCHAR(500), @mas INT, @pla BIT, @mem BIT, @gue BIT
  DECLARE @ID INT, @RLR VARCHAR(500)
  DECLARE @Role TABLE ( [Role] VARCHAR(100) )
  
  DECLARE LISTROLE CURSOR LOCAL FAST_FORWARD FOR
    SELECT Album, [Master], Performer, Play, Member, dbo.RoleHideTrack( [Role] ), SpecialGuest
      FROM Inserted
  OPEN LISTROLE
  FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- Есть запись?
    IF EXISTS( SELECT * FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)) BEGIN
      SELECT @ID = ID, @RLR = dbo.RoleHideTrack( [Role] ) 
        FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)
      -- Есть роль?
      INSERT INTO @Role SELECT DISTINCT TRIM( value ) FROM STRING_SPLIT( @RLR, ',' )
      INSERT INTO @Role SELECT TRIM( value ) FROM STRING_SPLIT( @ROL, ',' ) 
                                               WHERE TRIM( value ) NOT IN ( SELECT [Role] FROM @Role )
      UPDATE @Role SET [Role] = TRIM( [Role] )
      DELETE @Role WHERE ([Role] = '')
      SELECT @RLR = dbo.RoleRestTrack( STRING_AGG( [Role], ', ' )) FROM @Role
      -- Обновление списка.
      UPDATE AlbumRole SET [Role] = @RLR WHERE ID = @ID
    END ELSE BEGIN
      -- Добавление: 
      SET @ROL = dbo.RoleRestTrack( @ROL )
      INSERT INTO AlbumRole ( Album, [Master], Performer, Play, Member, [Role], SpecialGuest ) 
             VALUES( @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue )
    END
    -- Следующее поле
    FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  END
  CLOSE LISTROLE
  DEALLOCATE LISTROLE
END

Since there are notes in the lists and they can be split, we ,replace them before processing and restore them at the end:

CREATE FUNCTION dbo.RoleHideTrack ( @RL VARCHAR(500) ) RETURNS VARCHAR(500)
WITH EXECUTE AS CALLER
AS
BEGIN
  DECLARE @TR VARCHAR(100), @HR VARCHAR(100)
  WHILE (CHARINDEX( '(', @RL) > 0 ) BEGIN
    SET @TR = SUBSTRING( @RL, CHARINDEX( '(', @RL), CHARINDEX( ')', @RL) - CHARINDEX( '(', @RL) + 1)
    SET @HR = REPLACE( REPLACE( REPLACE( @TR, ')', '}' ), '(', '{' ), ',', ';')
    SET @RL = REPLACE( @RL, @TR, @HR )
  END
  RETURN @RL
END

CREATE FUNCTION dbo.RoleRestTrack ( @RL VARCHAR(500) ) RETURNS VARCHAR(500)
WITH EXECUTE AS CALLER
AS
BEGIN
  RETURN REPLACE( REPLACE( REPLACE( @RL, '}', ')' ), '{', '(' ), ';', ',')
END