我在sql server 2008R2中有以下源和目标表.如何在Tsql中进行枢纽以从源头到达目的地.
SourceTbl
empId empIndex empState empStDate empEndDate ======================================================== 10 1 AL 1/1/2012 12/1/2012 10 2 FL 2/1/2012 2/1/2013 15 1 FL 3/20/2012 1/1/2099
DestTbl
empId empState1 empState1StDate empState1EndDt empState2 empState2StDate empState2EndDt ========================================================================================================= 10 AL 1/1/2012 12/1/2012 FL 2/1/2012 2/1/2013 15 FL 3/20/2012 1/1/2099 NULL NULL NULL
希望empIndex将以某种方式帮助您.
解决方法
由于您使用的是sql Server,因此可以通过多种不同的方式将行转换为列.您可以使用具有CASE表达式的聚合函数：
select empid,max(case when empindex = 1 then empstate end) empState1,max(case when empindex = 1 then empStDate end) empStDate1,max(case when empindex = 1 then empEndDate end) empEndDate1,max(case when empindex = 2 then empstate end) empState2,max(case when empindex = 2 then empStDate end) empStDate2,max(case when empindex = 2 then empEndDate end) empEndDate2 from sourcetbl group by empid;
如果要使用PIVOT函数来获取结果,那么我建议首先解除列empState,empStDate和empEndDate,这样您将首先有多个行.您可以使用UNPIVOT功能或CROSS APPLY转换代码的数据：
select empid,col+cast(empindex as varchar(10)) col,value from sourcetbl cross apply ( select 'empstate',empstate union all select 'empstdate',convert(varchar(10),empstdate,120) union all select 'empenddate',empenddate,120) ) c (col,value);
请参阅Demo.一旦数据被无法使用,那么您可以应用PIVOT函数,以便最终的代码是：
select empid,empState1,empStDate1,empEndDate1,empState2,empStDate2,empEndDate2
from
(
select empid,value
from sourcetbl
cross apply
(
select 'empstate',empstate union all
select 'empstdate',120) union all
select 'empenddate',120)
) c (col,value)
) d
pivot
(
max(value)
for col in (empState1,empEndDate2)
) piv;
如果您的数量有限,则上述版本将会很好,但是如果不是,则可以使用动态sql：
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(empindex as varchar(10)))
from SourceTbl
cross apply
(
select 'empstate',1 union all
select 'empstdate',2 union all
select 'empenddate',3
) c (col,so)
group by col,so,empindex
order by empindex,so
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,'')
set @query = 'SELECT empid,' + @cols + '
from
(
select empid,value
from sourcetbl
cross apply
(
select ''empstate'',empstate union all
select ''empstdate'',120) union all
select ''empenddate'',120)
) c (col,value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute sp_executesql @query;
您可以使用这些查询来INSERT INTO您的DestTbl,或者不是以此格式存储数据,您现在可以查询以获得所需的结果.
这些查询以数据格式出现：
| EMPID | EMPSTATE1 | EMPSTDATE1 | EMPENDDATE1 | EMPSTATE2 | EMPSTDATE2 | EMPENDDATE2 | --------------------------------------------------------------------------------------- | 10 | AL | 2012-01-01 | 2012-12-01 | FL | 2012-02-01 | 2013-02-01 | | 15 | FL | 2012-03-20 | 2099-01-01 | (null) | (null) | (null) |
