将行数据透视到多个列

can we pivot rows to multiple columns i.e

我们能把行轴转到多列吗

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

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

or any-other way. Most records (p_id) will have fixed number of columns (Custom Name) heading - few some and some none. Many thanks

或任何其他方式。大多数记录(p_id)都有固定数量的列(自定义名称)标题——一些有一些没有。非常感谢

2 个解决方案

#1

Try this. I'd suggest reading up on pivots on your own since they're a little bit funky. You can do that here: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

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

The one thing you'll notice is I coalesced all the columns into a single data type (string), because trying to do it across multiple columns is a nightmare. If you still need to enforce the data types, I'd do that in the final select.

您将注意到的一件事是，我将所有列合并到一个数据类型(string)中，因为跨多个列执行此操作是一场噩梦。如果您仍然需要强制执行数据类型，我将在最后的select中这样做。

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

if for some reason you're hellbent on pivoting multiple columns, you'll have to do multiple pivots.

如果由于某些原因，您要转向多个列，您将不得不执行多个轴心。

#2

Two quick options:

两个快速选择:

1) Conditional Aggregation

1)条件聚合

select P_ID
      ,DepartmentCode = max(case when Custom_Name='DepartmentCode' then Text_Value end) 
      ,Year           = max(case when Custom_Name='Year'           then Text_Value end) 
      ,StartDate      = max(case when Custom_Name='StartDate'      then DateTime   end) 
      ,EmpID          = max(case when Custom_Name='EmpID'          then Number_Value end) 
      ,EmpTitle       = max(case when Custom_Name='EmpTitle'       then Text_Value end) 
from #Temp_Trans
Group By P_ID

2) Dynamic Pivot

2)动态主

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName(Custom_Name) From #Temp_Trans For XML Path('')),1,1,'')   
Select  @SQL = 'Select P_ID,' + @SQL + ' 
                From (
                       Select P_ID
                             ,ITEM  = Custom_Name
                             ,Value = concat(Text_Value,Number_Value,format(DateTime,''yyyy-MM-dd''))
                       From  #Temp_Trans
                     ) A
                Pivot (max(Value) For Item in (' + @SQL + ') ) p'
Exec(@SQL);

#1