代码之家 › 专栏 › 技术社区 › cooolstaff

Sql列到行联接表

unpivot sql

-1

cooolstaff · 技术社区 · 12 年前

我有两张这样的桌子。

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

我试着得到这样的结果

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

我只是试着做不吃东西,但没能和旁边的另一张桌子连在一起。能帮帮我吗?

2 回复 | 直到 12 年前

1

Taryn Frank Pearson 12 年前

如果您使用的是SQL Server 2008+,那么您可以 不吃东西的 Table1 使用 CROSS APPLY 用一个 VALUES 子句,然后加入 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;

看见 SQL Fiddle with Demo

2

1

RichardTheKiwi 12 年前

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;