SQL Server 2017

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

CREATE TABLE [dbo].[TestY] (
[TYPE] varchar(1) NULL ,
[MODE] varchar(7) NULL );
--
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'R', N'1101111');
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'Q', N'1111000');
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'T', N'1110111');
--
SELECT *
FROM TestY;


TYPE
MODE


R
1101111

Q
1111000

T
1110111


CREATE TABLE [dbo].[TestZ] (
[TYPE] varchar(1) NULL ,
[CODESTRING] varchar(10) NULL ,
[CODE] varchar(1) NULL );
--
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'1xx', N'A');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'2xx', N'B');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'3xx', N'C');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'4xx', N'D');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'5xx', N'E');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'6xx', N'F');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'7xx', N'G');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'1xx', N'H');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'2xx', N'I');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'3xx', N'J');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'4xx', N'K');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'5xx', N'L');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'6xx', N'M');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'7xx', N'N');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'1xx', N'P');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'2xx', N'Q');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'3xx', N'R');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'4xx', N'W');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'5xx', N'X');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'6xx', N'Y');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'7xx', N'Z');
--
SELECT *
FROM TestZ;


TYPE
CODESTRING
CODE


R
1xx
A

R
2xx
B

R
3xx
C

R
4xx
D

R
5xx
E

R
6xx
F

R
7xx
G

Q
1xx
H

Q
2xx
I

Q
3xx
J

Q
4xx
K

Q
5xx
L

Q
6xx
M

Q
7xx
N

T
1xx
P

T
2xx
Q

T
3xx
R

T
4xx
W

T
5xx
X

T
6xx
Y

T
7xx
Z


-- 每個字用 , 分隔 (REPLACE 2 次: 1 次對 1  , 1 次對 0)
SELECT TYPE,
LEFT(
REPLACE(
REPLACE(MODE,'1','1,'),'0','0,'),13) AS TempMode
FROM TestY AS A


TYPE
TempMode


R
1,1,0,1,1,1,1

Q
1,1,1,1,0,0,0

T
1,1,1,0,1,1,1


-- 將分隔的字依照 TYPE 欄位不同透過 CROSS APPLY STRING_SPLIT 轉成 value 欄位 , 
-- 並且排序產生 ROWID
SELECT *,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY TYPE
) AS ROWID
--
FROM (
SELECT TYPE,
LEFT(
REPLACE(
REPLACE(MODE,'1','1,'),'0','0,'),13) AS TempMode
FROM TestY AS A
) AS B
CROSS APPLY STRING_SPLIT(B.TempMode, ',') AS C


TYPE
TempMode
value
ROWID


Q
1,1,1,1,0,0,0
1
1

Q
1,1,1,1,0,0,0
1
2

Q
1,1,1,1,0,0,0
1
3

Q
1,1,1,1,0,0,0
1
4

Q
1,1,1,1,0,0,0
0
5

Q
1,1,1,1,0,0,0
0
6

Q
1,1,1,1,0,0,0
0
7

R
1,1,0,1,1,1,1
1
1

R
1,1,0,1,1,1,1
1
2

R
1,1,0,1,1,1,1
0
3

R
1,1,0,1,1,1,1
1
4

R
1,1,0,1,1,1,1
1
5

R
1,1,0,1,1,1,1
1
6

R
1,1,0,1,1,1,1
1
7

T
1,1,1,0,1,1,1
1
1

T
1,1,1,0,1,1,1
1
2

T
1,1,1,0,1,1,1
1
3

T
1,1,1,0,1,1,1
0
4

T
1,1,1,0,1,1,1
1
5

T
1,1,1,0,1,1,1
1
6

T
1,1,1,0,1,1,1
1
7


-- 上面的子 SQL 在用 E.TYPE=D.TYPE AND LEFT(E.CODESTRING,1)=D.ROWID 
-- 串 LEFT JOIN TestZ (表二) , 並依指定排序 RQT 排序
SELECT D.TYPE,
CASE WHEN value = 1 THEN CODE ELSE '' END
AS MODE2
--
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY TYPE
) AS ROWID
--
FROM (
SELECT TYPE,
LEFT(
REPLACE(
REPLACE(MODE,'1','1,'),'0','0,'),13) AS TempMode
FROM TestY AS A
) AS B
CROSS APPLY STRING_SPLIT(B.TempMode, ',') AS C
) AS D 
LEFT JOIN TestZ AS E ON E.TYPE=D.TYPE AND LEFT(E.CODESTRING,1)=D.ROWID
ORDER BY 
CASE WHEN D.TYPE='R' THEN 1
     WHEN D.TYPE='Q' THEN 2
     WHEN D.TYPE='T' THEN 3 
END


TYPE
MODE2


R
A

R
B

R


R
D

R
E

R
F

R
G

Q
H

Q
I

Q
J

Q
K

Q


Q


Q


T
P

T
Q

T
R

T


T
X

T
Y

T
Z


-- 最後再用 STRING_AGG 將字串依照 TYPE 欄位聚合
SELECT D.TYPE,
STRING_AGG(
CASE WHEN value = 1 THEN CODE ELSE '' END,'') 
AS MODE2
--
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY TYPE
) AS ROWID
--
FROM (
SELECT TYPE,
LEFT(
REPLACE(
REPLACE(MODE,'1','1,'),'0','0,'),13) AS TempMode
FROM TestY AS A
) AS B
CROSS APPLY STRING_SPLIT(B.TempMode, ',') AS C
) AS D 
LEFT JOIN TestZ AS E ON E.TYPE=D.TYPE AND LEFT(E.CODESTRING,1)=D.ROWID
GROUP BY D.TYPE
ORDER BY 
CASE WHEN D.TYPE='R' THEN 1
     WHEN D.TYPE='Q' THEN 2
     WHEN D.TYPE='T' THEN 3 
END


TYPE
MODE2


R
ABDEFG

Q
HIJK

T
PQRXYZ

TYPE	CODESTRING	CODE
R	1xx	A
R	2xx	B
R	3xx	C
R	4xx	D
R	5xx	E
R	6xx	F
R	7xx	G
Q	1xx	H
Q	2xx	I
Q	3xx	J
Q	4xx	K
Q	5xx	L
Q	6xx	M
Q	7xx	N
T	1xx	P
T	2xx	Q
T	3xx	R
T	4xx	W
T	5xx	X
T	6xx	Y
T	7xx	Z

TYPE	TempMode	value	ROWID
Q	1,1,1,1,0,0,0	1	1
Q	1,1,1,1,0,0,0	1	2
Q	1,1,1,1,0,0,0	1	3
Q	1,1,1,1,0,0,0	1	4
Q	1,1,1,1,0,0,0	0	5
Q	1,1,1,1,0,0,0	0	6
Q	1,1,1,1,0,0,0	0	7
R	1,1,0,1,1,1,1	1	1
R	1,1,0,1,1,1,1	1	2
R	1,1,0,1,1,1,1	0	3
R	1,1,0,1,1,1,1	1	4
R	1,1,0,1,1,1,1	1	5
R	1,1,0,1,1,1,1	1	6
R	1,1,0,1,1,1,1	1	7
T	1,1,1,0,1,1,1	1	1
T	1,1,1,0,1,1,1	1	2
T	1,1,1,0,1,1,1	1	3
T	1,1,1,0,1,1,1	0	4
T	1,1,1,0,1,1,1	1	5
T	1,1,1,0,1,1,1	1	6
T	1,1,1,0,1,1,1	1	7

TYPE	CODESTRING	CODE
R	1xx	A
R	2xx	B
R	3xx	C
R	4xx	D
R	5xx	E
R	6xx	F
R	7xx	G
Q	1xx	H
Q	2xx	I
Q	3xx	J
Q	4xx	K
Q	5xx	L
Q	6xx	M
Q	7xx	N
T	1xx	P
T	2xx	Q
T	3xx	R
T	4xx	W
T	5xx	X
T	6xx	Y
T	7xx	Z

TYPE	CODESTRING	CODE
R	1xx	A
R	2xx	B
R	3xx	C
R	4xx	D
R	5xx	E
R	6xx	F
R	7xx	G
Q	1xx	H
Q	2xx	I
Q	3xx	J
Q	4xx	K
Q	5xx	L
Q	6xx	M
Q	7xx	N
T	1xx	P
T	2xx	Q
T	3xx	R
T	4xx	W
T	5xx	X
T	6xx	Y
T	7xx	Z