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


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