CODEWITHSUNDEEP
sqlsql-serverssis-2012
hana
hana

Reputation: 121

Pivot some row values to a columns

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

Upvotes: 1

Views: 123

Answers (5)

hana
hana

Reputation: 121

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;

Upvotes: 1

Ulysses
Ulysses

Reputation: 417

Venkataraman R your answer was very close, I tweaked it a bit to match his output.

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;

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

From your example, I assume that you want to map the Van_ian with other names, if the van_ian kod is start of the id of names. E.g, 01 is start of 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 |
+-----+--------+-------+---------+

Upvotes: 1

Turo
Turo

Reputation: 4914

The straight forward approach I can think of is a self join, where one id starts with the other

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

This should work for your example, but if the are also longer ids, the result may be strange

Upvotes: 0

itsSKP
itsSKP

Reputation: 134

I am observing a pattern in the values of ID. For example, IDs 0121, 0122 can be categorised to ID 01. Similarly, IDs 0211, 0221 can be categorised to ID 02. Based on this assumption, this query should do :

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

Upvotes: 0

Related Questions