SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Type (
[id] varchar(20),
[1_1] varchar(20),	
[2_1] varchar(20),	
[3_1] varchar(20),	
[3_2] varchar(20));

INSERT INTO Type ([id],[1_1],[2_1],[3_1],[3_2])
VALUES 
('ss','q','w','ee','r'),
('gg','uq','dw','ke','sr');

SELECT *
FROM Type


id
1_1
2_1
3_1
3_2


ss
q
w
ee
r

gg
uq
dw
ke
sr


CREATE TABLE SubType (
[type_id] varchar(20),
[sub_type_id] varchar(20),	
[text_ch] varchar(20));

INSERT INTO SubType ([type_id],[sub_type_id],[text_ch])
VALUES 
('1','q','name1'),
('6','w','name1'),
('3','dd','name1'),
('9','r','name1');

SELECT *
FROM SubType


type_id
sub_type_id
text_ch


1
q
name1

6
w
name1

3
dd
name1

9
r
name1


-- 方法一
SELECT b.[type_id] as source_type_id , b.[text_ch],
CASE WHEN b.[sub_type_id]=a.[1_1] THEN '[1_1]' 
WHEN b.[sub_type_id]=a.[2_1] THEN '[2_1]' 
WHEN b.[sub_type_id]=a.[3_1] THEN '[3_1]'
WHEN b.[sub_type_id]=a.[3_2] THEN '[3_2]'
END AS value
FROM Type AS a
INNER JOIN SubType AS b 
ON a.[1_1] = b.[sub_type_id] 
OR a.[2_1] = b.[sub_type_id]
OR a.[3_1] = b.[sub_type_id]
OR a.[3_2] = b.[sub_type_id]
WHERE a.id = 'ss'


source_type_id
text_ch
value


1
name1
[1_1]

6
name1
[2_1]

9
name1
[3_2]


-- 方法二
SELECT b.[type_id] AS source_type_id , b.[text_ch], a.value
FROM (
SELECT id,'[1_1]' AS value,[1_1] AS temp
FROM Type 
UNION  
SELECT id,'[2_1]' AS value,[2_1] AS temp
FROM Type 
UNION 
SELECT id,'[3_1]' AS value,[3_1] AS temp
FROM Type 
UNION 
SELECT id,'[3_2]' AS value,[3_2] AS temp
FROM Type) AS a
INNER JOIN SubType AS b ON a.[temp] = b.[sub_type_id] 
WHERE a.id = 'ss'


source_type_id
text_ch
value


1
name1
[1_1]

6
name1
[2_1]

9
name1
[3_2]


-- 方法三
SELECT b.[type_id] AS source_type_id , b.[text_ch], '['+a.value+']' AS value
FROM Type UNPIVOT(temp FOR [value] IN ([1_1],[2_1],[3_1],[3_2])) a
INNER JOIN SubType AS b ON a.[temp] = b.[sub_type_id] 
WHERE a.id = 'ss'


source_type_id
text_ch
value


1
name1
[1_1]

6
name1
[2_1]

9
name1
[3_2]