SQL与非平衡数据枢轴

Question

如果可以“填满”未包含在主表中的数据，我正在使用枢轴功能进行剥离。 我的表包含以下数据：

create table tmpData (objID INT, colID varchar(5), value varchar(50)); 
insert into tmpData (objId, colId, value) values(21, 'col1', 'a value'); 
insert into tmpData (objId, colId, value) values(21, 'col2', 'col2_1'); 
insert into tmpData (objId, colId, value) values(21, 'col2', 'col2_2_x'); -- a second 'value' for col_2 
insert into tmpData (objId, colId, value) values(21, 'col3', 'col3_1'); 
insert into tmpData (objId, colId, value) values(22, 'col1', 'another value'); 
insert into tmpData (objId, colId, value) values(22, 'col2', 'col2_2'); 
insert into tmpData (objId, colId, value) values(22, 'col3', 'col3_2'); 

随着旋转功能

select 
* 
from (
select 
    objID 
, colID 
, value 
from tmpData) 
t 
PIVOT (MAX(value) for colID in ([col1], [col2], [col3])) pivottable; 

我COL2只得到一个（最大）值的ObjID = 21：

objID col1   col2   col3 
21 a value  col2_2_x  col3_1 
22 another value col2_2  col3_2 

我喜欢什么得到的是所有值并且填充了col1和col3中objID = 21的非给定数据：

objID col1   col2  col3 
21 a value  col2_2  col3_1 
21 a value  col2_2_x col3_1 
22 another value col2_2  col3_2 

这可能与枢轴功能或以其他方式？ 提前 约尔格

Answer 1

非常感谢你似乎（在某种程度上）想在列名单。如果你能够接受这个结果住：

select objId, 
     max(case when colId = 'col1' then value end) as col1, 
     max(case when colId = 'col2' then value end) as col2, 
     max(case when colId = 'col3' then value end) as col3 
from (select d.*, 
      dense_rank() over (partition by objId, colId order by (select NULL)) as seqnum 
     from tmpData d.* 
    ) t 
group by objId, seqnum; 

在SQL Server 2012+采用累积max()，你可以做你想做什么：

objID col1   col2  col3 
21 a value  col2_2  col3_1 
21 NULL   col2_2_x NULL 
22 another value col2_2  col3_2 

然后你就可以通过枚举值做到这一点

select objId, 
     max(max(case when colId = 'col1' then value end)) over (partition by objId order by seqnum) as col1, 
     max(max(case when colId = 'col2' then value end)) over (partition by objId order by seqnum) as col2, 
     max(max(case when colId = 'col3' then value end)) over (partition by objId order by seqnum) as col3 
from (select d.*, 
      dense_rank() over (partition by objId, colId order by (select value)) as seqnum 
     from tmpData d.* 
    ) t 
group by objId, seqnum; 

请注意，dense_rank()中的order by已被更改为按值显式排序。

Answer 2

使用CTE和左加入摆脱COL1和COL3值都COL2值

;with 
t as (select distinct objID from #tmpData), 
t1 as (select objID, value col1 from #tmpData where colID = 'col1'), 
t2 as (select objID, value col2 from #tmpData where colID = 'col2'), 
t3 as (select objID, value col3 from #tmpData where colID = 'col3') 
select t.objID, col1, col2, col3 
from t 
left join t1 on t.objID = t1.objID 
left join t2 on t.objID = t2.objID 
left join t3 on t.objID = t3.objID 
order by t.objID 

这也将工作，如果你有COL1或COL3