将某些行值透视到列

Question

我正在尝试将一些行值透视到一列，以下是我想要实现的目标。

我当前的架构：

+------+----------+
|  ID  |   name   |
+------+----------+
|   01 |  Vsp lan |
| 0121 |  abn     |
| 0122 |  abb     |
| 0123 |  vsp     |
|   02 |  Apn lan |
| 0211 |  add     |
| 0221 |  acd     |
+------+----------+

这就是我想要实现的目标：

+-----+--------+-------+---------+
| kod |   ID   | name  |   lan   |
+-----+--------+-------+---------+
|  01 |   0121 |   abn | vsp lan |
|  01 |   0122 |   abb | vsp lan |
|  01 |   0123 |   vsp | vsp lan |
|  02 |   0211 |   add | Apn lan |
|  02 |   0221 |   acd | Apn lan |
+-----+--------+-------+---------+

但是当名称和 LAN 具有相似的值时，它会跳过行，在这种情况下，它会跳过名称值为 VSP 的行。

DECLARE @table TABLE (ID VARCHAR(5),[name] VARCHAR(10));
INSERT INTO @table
VALUES
('01','Vsp Ian')
,('0121','abn')
,('0122','abb')
,('0123','vsp')
,('02','Apn Ian')
,('0211','add')
,('0221','acd')
;
SELECT a.id as kod, b.id as ID, B.name as name, a.name as lan
FROM @table a
inner join @table b on CHARINDEX(a.id,b.id) = 1 and CHARINDEX(b.name,a.name) = 0

Answer 1

从您的示例中，我假设您希望将Van_ian与其他名称映射，如果van_iankod是名称 id 的开头。例如，01 是 0121、0122 的开头。

DECLARE @table table(id CHAR(10), name varchar(10))
INSERT INTO @table
values
('01','vsp ian'),
('0121','abn'),
('0122','abb'),
('02','vsp ian'),
('0211','add'),
('0221','acd');
SELECT ct.id as kod,ot.id, ot.name,ct.name as ian FROM @table as ot
CROSS JOIN
(SELECT distinct id, name from @table WHERE name like 'vsp%'
) as ct(id,name)
WHERE ot.name not like 'vsp%'
and LEFT(ot.id,2) = ct.id

+-----+--------+-------+---------+
| kod |   id   | name  |   ian   |
+-----+--------+-------+---------+
|  01 |   0121 |   abn | vsp ian |
|  01 |   0122 |   abb | vsp ian |
|  02 |   0211 |   add | vsp ian |
|  02 |   0221 |   acd | vsp ian |
+-----+--------+-------+---------+

Answer 2

这解决了我的问题。

DECLARE @table TABLE (ID VARCHAR(5),[name] VARCHAR(10));
INSERT INTO @table
VALUES
('01','Vsp Ian')
,('0121','abn')
,('0122','abb')
,('0123','vsp')
,('02','Apn Ian')
,('0211','add')
,('0221','acd')
,('03','Ubb Ian')
,('0301','afg')
,('0302','ampx');

SELECT
kod = ct.id
,ot.ID
,ot.[name]
,Ian = ct.[name]
FROM @table ot
CROSS JOIN (SELECT ID,[name] FROM @table WHERE [name] LIKE '%[lan]%') ct
WHERE len(ot.ID) > 2 
AND LEFT(ot.id,2) = ct.id;

Answer 3

我观察到ID值的模式。例如，可以将 ID0121, 0122分类为 ID01。同样，ID0211, 0221可以分类为 ID02。 基于此假设，此查询应执行以下操作：

select 
tb1.ID as kod, 
tb2.ID as ID, 
tb2.name as name, 
tb1.name as laen 
from tableName tb1 join tableName tb2 
on tb1.ID != tb2.ID and LOCATE(tb1.ID,tb2.ID) = 1

Answer 4

我能想到的直接方法是自加入，其中一个 id 以另一个 id 开头

SELECT a.id as kod, b.id as ID, B.name as name, a.name as laen
FROM table_name a
inner join table_name b on CHARINDEX(a.name,b.name) = 1 and CHARINDEX(b.name,a.name) = 0

这应该适用于您的示例，但如果 ID 也较长，则结果可能会很奇怪

Answer 5

文卡塔拉曼 R 你的答案非常接近，我稍微调整了一下以匹配他的输出。

DECLARE @table TABLE (ID VARCHAR(5),[name] VARCHAR(10));
INSERT INTO @table
VALUES
('01','Vsp_Ian')
,('0121','abn')
,('0122','abb')
,('02','Apn_Ian')
,('0211','add')
,('0221','acd')
,('03','Ubb_Ian')
,('0301','afg')
,('0302','ampx');
SELECT
kod = ct.id
,ot.ID
,ot.[name]
,Ian = ct.[name]
FROM @table ot
CROSS JOIN (SELECT ID,[name] FROM @table WHERE [name] LIKE '%[_]%') ct
WHERE ot.[name] NOT LIKE '%[_]%'
AND LEFT(ot.id,2) = ct.id;