CREATE TABLE [dbo].[recommend_v1] (
[filedate] varchar(40) NULL ,
[layout1] varchar(40) NULL ,
[layout3] varchar(40) NULL );
--
INSERT INTO [dbo].[recommend_v1] ([filedate], [layout1], [layout3]) 
VALUES (N'QQ', N'A,B,X,Y,Z,F,G,X,Y,Z,F,G',N'A,B,C,D,E,F,G,A,B,C,D');
INSERT INTO [dbo].[recommend_v1] ([filedate], [layout1], [layout3]) 
VALUES (N'RR', N'A,B,C,D,E,F,G,A,B,C,D',N'P,Q,R,D,E,F,G,R,D,E,F,G');
INSERT INTO [dbo].[recommend_v1] ([filedate], [layout1], [layout3]) 
VALUES (N'TT', N'P,Q,R,D,E,F,G,R,D,E,F,G',N'A,B,X,Y,Z,F,G,X,Y,Z,F,G');

-- case 1: 依 tag 計數
SELECT C.value AS tag,COUNT(C.value) AS counter
FROM recommend_v1 AS B
-- 將 layout1+','+layout3 併成 Newlayout ,同時依 ',' 字元拆成多筆資料
CROSS APPLY STRING_SPLIT(B.layout1+','+layout3, ',') AS C
GROUP BY C.value
ORDER BY C.value

-- case 2 : 依 filedate 及 tag 計數
SELECT filedate,C.value AS tag,COUNT(C.value) AS counter
FROM recommend_v1 AS B
-- 將 layout1+','+layout3 併成 Newlayout ,同時依 ',' 字元拆成多筆資料
CROSS APPLY STRING_SPLIT(B.layout1+','+layout3, ',') AS C
GROUP BY filedate,C.value
ORDER BY filedate,C.value

Demo

刪...