I have got two tables like that .
---------------------------------------------------
| AltID | Alan 1| Alan 2 | Alan 3 | UserId |
---------------------------------------------------
| 1 | Opel | null | Engine | 5121 |
| 2 | Home | Garden | null | 5653 |
| 3 | null | null | null | 2133 |
---------------------------------------------------
-------------------------------------
| UserId | Kolon | NewValue |
-------------------------------------
| 2133 | Alan 1 | null |
| 2133 | Alan 2 | null |
| 2133 | Alan 3 | null |
| 5121 | Alan 1 | Brand |
| 5121 | Alan 2 | Model |
| 5121 | Alan 3 | Part |
| 5653 | Alan 1 | Place |
| 5653 | Alan 2 | Point |
| 5653 | Alan 3 | Take |
-------------------------------------
I try to get a result like that
-----------------------------------------------------
| UserId | Kolon | NewValue | AltID | Deger |
-----------------------------------------------------
| 2133 | Alan 1 | null | 3 | null |
| 2133 | Alan 2 | null | 3 | null |
| 2133 | Alan 3 | null | 3 | null |
| 5121 | Alan 1 | Brand | 1 | Opel |
| 5121 | Alan 2 | Model | 1 | null |
| 5121 | Alan 3 | Part | 1 | Engine|
| 5653 | Alan 1 | Place | 2 | Home |
| 5653 | Alan 2 | Point | 2 | Garden|
| 5653 | Alan 3 | Take | 2 | null |
-----------------------------------------------------
I just try to do unpivot but couldnt join other table to next it. Could pls help me?
If you are using SQL Server 2008+, then you can unpivot Table1 using CROSS APPLY with a VALUES clause and then join on Table2:
select t1.userid,
t2.Kolon,
t2.newvalue,
t1.altid,
t1.Deger
from
(
select AltId, col, Deger, UserId
from table1 t1
cross apply
(
values ('Alan 1', [Alan 1]),
('Alan 2', [Alan 2]),
('Alan 3', [Alan 3])
) c (col, Deger)
) t1
inner join table2 t2
on t1.userid = t2.userid
and t1.col = t2.Kolon
order by t1.userid;