将两列PIVOT保持原样 [英] PIVOT two columns and keep others as they are

问题描述

我想将某些行转换为列，同时保持其他行不变.

I want to turn some of the rows into columns while keeping other rows as they are.

ID      name     value     RefId
1       Fname    John      32145
2       LName    Smith     32145
3       Fname    Peter     34589
4       LName    Mahlang   34589

现在我要实现的是将Fname和Lname行变成具有匹配的value字段的列. ID列并不重要，我不需要.

Now what I want to achieve is to turn the Fname and Lname rows into columns with their matching value field. ID column doesn't really matter, I don't need it.

所需的输出

       Fname     Lname     RefId
       John      Smith     32145
       Peter     Mahlang   34589

任何帮助

推荐答案

使用条件聚合:

select 
    Fname = max(case when name = 'Fname' then value end)
  , Lname = max(case when name = 'Lname' then value end)
  , RefId
from t
group by RefId

rextester演示: http://rextester.com/MRMY11592

rextester demo: http://rextester.com/MRMY11592

返回:

+---------+---------+-------+
|  Fname  |  Lname  | RefId |
+---------+---------+-------+
| John    | Smith   | 32145 |
| Peter   | Mahlang | 34589 |
+---------+---------+-------+

---

或使用pivot()

select 
    Fname
  , Lname
  , RefId
from (select name, value, refid from t) s
pivot(max(value) for name in ([Fname],[Lname]))p

这篇关于将两列PIVOT保持原样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！