Pivot some row values to a columns

Question

I am trying to pivot some row values to a column, below is what I am trying to achieve.

My Current Schema:

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

This is what I am trying to achieve:

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

but it skips rows when name and lan have similar values, in this case it skips the row with the name value 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

this fixes my problem.

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;