SQL Server 2017

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

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');
--
SELECT *
FROM recommend_v1;


filedate
layout1
layout3


QQ
A,B,X,Y,Z,F,G,X,Y,Z,F,G
A,B,C,D,E,F,G,A,B,C,D

RR
A,B,C,D,E,F,G,A,B,C,D
P,Q,R,D,E,F,G,R,D,E,F,G

TT
P,Q,R,D,E,F,G,R,D,E,F,G
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


tag
counter


A
6

B
6

C
4

D
8

E
6

F
10

G
10

P
2

Q
2

R
4

X
4

Y
4

Z
4


-- 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


filedate
tag
counter


QQ
A
3

QQ
B
3

QQ
C
2

QQ
D
2

QQ
E
1

QQ
F
3

QQ
G
3

QQ
X
2

QQ
Y
2

QQ
Z
2

RR
A
2

RR
B
2

RR
C
2

RR
D
4

RR
E
3

RR
F
3

RR
G
3

RR
P
1

RR
Q
1

RR
R
2

TT
A
1

TT
B
1

TT
D
2

TT
E
2

TT
F
4

TT
G
4

TT
P
1

TT
Q
1

TT
R
2

TT
X
2

TT
Y
2

TT
Z
2

filedate	layout1	layout3
QQ	A,B,X,Y,Z,F,G,X,Y,Z,F,G	A,B,C,D,E,F,G,A,B,C,D
RR	A,B,C,D,E,F,G,A,B,C,D	P,Q,R,D,E,F,G,R,D,E,F,G
TT	P,Q,R,D,E,F,G,R,D,E,F,G	A,B,X,Y,Z,F,G,X,Y,Z,F,G

filedate	tag	counter
QQ	A	3
QQ	B	3
QQ	C	2
QQ	D	2
QQ	E	1
QQ	F	3
QQ	G	3
QQ	X	2
QQ	Y	2
QQ	Z	2
RR	A	2
RR	B	2
RR	C	2
RR	D	4
RR	E	3
RR	F	3
RR	G	3
RR	P	1
RR	Q	1
RR	R	2
TT	A	1
TT	B	1
TT	D	2
TT	E	2
TT	F	4
TT	G	4
TT	P	1
TT	Q	1
TT	R	2
TT	X	2
TT	Y	2
TT	Z	2

filedate	tag	counter
QQ	A	3
QQ	B	3
QQ	C	2
QQ	D	2
QQ	E	1
QQ	F	3
QQ	G	3
QQ	X	2
QQ	Y	2
QQ	Z	2
RR	A	2
RR	B	2
RR	C	2
RR	D	4
RR	E	3
RR	F	3
RR	G	3
RR	P	1
RR	Q	1
RR	R	2
TT	A	1
TT	B	1
TT	D	2
TT	E	2
TT	F	4
TT	G	4
TT	P	1
TT	Q	1
TT	R	2
TT	X	2
TT	Y	2
TT	Z	2

filedate	tag	counter
QQ	A	3
QQ	B	3
QQ	C	2
QQ	D	2
QQ	E	1
QQ	F	3
QQ	G	3
QQ	X	2
QQ	Y	2
QQ	Z	2
RR	A	2
RR	B	2
RR	C	2
RR	D	4
RR	E	3
RR	F	3
RR	G	3
RR	P	1
RR	Q	1
RR	R	2
TT	A	1
TT	B	1
TT	D	2
TT	E	2
TT	F	4
TT	G	4
TT	P	1
TT	Q	1
TT	R	2
TT	X	2
TT	Y	2
TT	Z	2