How to create a view for elements from lists?

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 create a view of unique items from lists?

Answer 1

CREATE VIEW RoleDivided
AS
  SELECT DISTINCT TRIM( value ) AS RoleSingle
    FROM AlbumRole 
      CROSS APPLY STRING_SPLIT( dbo.RoleDelTrack( [Role] ), ',' )

After removing the notes:

CREATE FUNCTION dbo.RoleDelTrack ( @RL VARCHAR(500) ) RETURNS VARCHAR(500)
WITH EXECUTE AS CALLER
AS
BEGIN
  WHILE (CHARINDEX( '(', @RL) > 0 ) BEGIN
    SET @RL = TRIM( LEFT( @RL, CHARINDEX( '(', @RL) - 1 ))
              + RIGHT( @RL, LEN( @RL ) - CHARINDEX( ')', @RL ))
  END
  RETURN @RL
END