Marius Marius - 1 month ago 5
SQL Question

SQL How to Decode a column by another column

I have a table

tab_1
with 3 columns
seller_name
,
id_seller
,
id_buyer
and i want to show another collumn named
buyer_name
witch corresponding to
id_buyer
.

The seller can be also buyer and that's why they have the same
id


| corresponding code |
-----------------------
abc = 1005
ddd = 1009
ccc = 1006
aaa = 1002
zzp = 1186
tyu = 1001





tab_1:

| seller_name | id_seller | id_buyer|
-----------------|----------------------
| abc | 1005 | 1006 |
| ddd | 1009 | 1186 |
| ccc | 1006 | 1001 |
| ccc | 1006 | 1002 |
| ddd | 1009 | 1006 |
| tyu | 1001 | 1186 |


The results i want is shown bellow:

| seller_name | id_seller |buyer_name| id_buyer|
-----------------|---------------------------------
| abc | 1005 | ccc | 1006 |
| ddd | 1009 | zzp | 1186 |
| ccc | 1006 | tyu | 1001 |
| ccc | 1006 | aaa | 1002 |
| ddd | 1009 | ccc | 1006 |
| tyu | 1001 | zzp | 1186 |

Answer
    create table #a

    ( buyer_name varchar(50), id_buyer int
    )
    insert into #a values
        ('abc',1005),
        ('ddd',1009),
        ('ccc',1006),
        ('aaa',1002),
        ('zzp',1186),
        ('tyu',1001)


        create table #b
        (

          seller_name varchar(50),   id_seller  int, id_buyer int
          )
          insert into #b values

     ('abc',1005,1006),   
     ('ddd',1009,1186),   
     ('ccc',1006,1001),   
     ('ccc',1006,1002),   
     ('ddd',1009,1006),   
     ('tyu',1001,1186) 

     select seller_name,id_seller,buyer_name,a.id_buyer from #a a join 
      #b b on a.id_buyer=b.id_buyer


or 

SELECT 
    seller.seller_name, seller.id_seller, isnull(person.name,seller.seller_name) AS buyer_name, seller.id_buyer 
FROM 
    #b seller 

    LEFT OUTER JOIN (SELECT DISTINCT seller_name AS name, id_seller AS id FROM #b) person ON 
        seller.id_buyer = person.id