SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- 宣告資料表變數
DECLARE @BOOK_TYPE TABLE (
  BOOK_ID INT,
  KEYWORD NVARCHAR(100),
  BOOK_COUNT INT
)

INSERT INTO @BOOK_TYPE (BOOK_ID, KEYWORD, BOOK_COUNT) VALUES (1, N'雜誌,普科,月刊', 33),(2, N'書籍,園藝', 99),(3, N'書籍,語言', 11),(4, N'書', 11);

DECLARE @splitter VARCHAR(10) = ',';
DECLARE @splitterlength INT = LEN(@splitter) + 1;

--第一層
  SELECT
    BOOK_ID,
    1 AS startpos,
    CHARINDEX(@splitter, KEYWORD + @splitter) - 1 AS endpos
  FROM @BOOK_TYPE
  WHERE LEN(KEYWORD) > 0

;WITH BOOK_TYPE_LIST AS
(
  SELECT
    BOOK_ID,
    1 AS startpos,
    CHARINDEX(@splitter, KEYWORD + @splitter) - 1 AS endpos
  FROM @BOOK_TYPE
  WHERE LEN(KEYWORD) > 0

UNION ALL

SELECT
    s.BOOK_ID,
    s.endpos + @splitterlength,  
    CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) - 1
  FROM BOOK_TYPE_LIST s 
    JOIN @BOOK_TYPE m 
      ON s.BOOK_ID = m.BOOK_ID 
      AND CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) > 0
)
SELECT
  m.BOOK_ID,
  SUBSTRING(m.KEYWORD, s.startpos, s.endpos - s.startpos + 1) AS KEYWORD,
  m.BOOK_COUNT
FROM @BOOK_TYPE m
JOIN BOOK_TYPE_LIST s ON m.BOOK_ID = s.BOOK_ID
ORDER BY m.BOOK_ID;

BOOK_ID	startpos	endpos
1	1	2
2	1	2
3	1	2
4	1	1

BOOK_ID	KEYWORD	BOOK_COUNT
1	雜誌	33
1	普科	33
1	月刊	33
2	園藝	99
2	書籍	99
3	書籍	11
3	語言	11
4	書	11