sql pivot 行到多列

Question

我们能否将行旋转到多列，即

Create table #Temp_Trans    

(   
P_ID int,   
Custom_Name varchar(30),    
Text_Value varchar(30), 
Number_Value int,   
[DateTime] datetime,    

)   

insert into #Temp_Trans values  
(1111,'DepartmentCode','AAA',null,null),    
(1111,'Year','2017',null,null), 
(1111,'StartDate',null,null,'2002-10-02'),  
(1111,'EmpID',null,555,null),   
(1111,'EmpTitle','TeamLeader',null,null),   

(2222,'DepartmentCode','BBB',null,null),    
(2222,'Year','2016',null,null), 
(2222,'StartDate',null,null,'2010-10-02'),  
(2222,'EmpID',null,null,null),  
(2222,'EmpTitle',null,null,null),   

(3333,'DepartmentCode','CCC',null,null),    
(3333,'Year','2017',null,null), 
(3333,'StartDate',null,null,'2017-10-02')   

select * from #Temp_Trans

http://sqlfiddle.com/#!6/d4eb9

或任何其他方式。大多数记录 (p_id) 将具有固定数量的列(自定义名称)标题 - 很少，有些没有。非常感谢

Answer 1

试试这个。我建议您自己阅读枢轴，因为它们有点时髦。你可以在这里做:https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

您会注意到的一件事是我将所有列合并为单一数据类型(字符串)，因为尝试跨多个列执行此操作是一场噩梦。如果您仍然需要强制执行数据类型，我会在最终选择中执行此操作。

select 
    p_id,
    DepartmentCode = cast(DepartmentCode as varchar(30)),
    Year = cast(Year as int),
    StartDate = cast(StartDate as datetime),
    EmpId = cast(EmpId as int),
    EmpTitle = cast(EmpTitle as varchar(30))
from (select 
          P_ID,
          custom_name,
          Value = coalesce(text_value, cast(number_value as varchar(30)), convert(varchar(30), datetime, 120))
      from #Temp_Trans) s
pivot(max(Value) for custom_name in (DepartmentCode, Year, StartDate, EmpID,EmpTitle))p 

如果出于某种原因您执意要旋转多列，则必须进行多次旋转。