Sql Column to Row joined tables

Question

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?

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn, Apr 03 '13 at 10:56

score 1 · Answer 1 · answered Apr 03 '13 at 10:57

1

select a.UserId, a.Kolon, a.NewValue, b.AltID,
       case a.Kolon when 'Alan 1' then [Alan 1]
                    when 'Alan 2' then [Alan 2]
                    when 'Alan 3' then [Alan 3] end Deger
  from Tbl2 a
  join Tbl1 b on a.UserId = b.UserId;

answered Apr 03 '13 at 10:57

RichardTheKiwi

105,798
26
196
262

score 1 · Accepted Answer · answered Apr 03 '13 at 11:01

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;

See SQL Fiddle with Demo

Sql Column to Row joined tables

2 Answers2