sql-server – SQL Server基于一列数据透视多列

我在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;

见SQL Fiddle with Demo.

如果要使用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 Fiddle with Demo.

如果您的数量有限,则上述版本将会很好,但是如果不是,则可以使用动态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;

请参见sql Fiddle with Demo

您可以使用这些查询来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) |

sql-server – SQL Server基于一列数据透视多列

解决方法

猜你在找的MsSQL相关文章