由于您使用的是 SQL Server，因此您可以通过多种不同的方式将行转换为列.您可以将聚合函数与 CASE 表达式一起使用:

Since you are using SQL Server there are several different ways that you can convert the rows into columns. You can use an aggregate function with a CASE expression:

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 来转换代码将是的数据:

If you want to use the PIVOT function to get the result, then I would recommend first unpivoting the columns empState, empStDate and empEndDate so you will have multiple rows first. You can use the UNPIVOT function or CROSS APPLY to convert the data the code will be:

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', convert(varchar(10), empenddate, 120)
) c (col, value);

参见演示.取消数据透视后，您可以应用 PIVOT 函数，这样最终代码将是:

See Demo. Once the data is unpivoted, then you can apply the PIVOT function so the final code will be:

select empid,
  empState1, empStDate1, empEndDate1,
  empState2, empStDate2, empEndDate2
from 
(
  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', convert(varchar(10), empenddate, 120)
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (empState1, empStDate1, empEndDate1,
              empState2, empStDate2, empEndDate2)
) piv;

参见 SQL Fiddle with Demo.

如果您的 empindex 数量有限，上述版本会很好用，但如果没有，那么您可以使用动态 SQL:

Th above versions will work great if you have a limited number of empindex, but if not then you can use dynamic 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,1,'')

set @query = 'SELECT empid,' + @cols + ' 
            from 
            (
                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'', convert(varchar(10), 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，或者不再以这种格式存储数据，您现在可以通过查询来获得所需的结果.

You can use these queries to INSERT INTO your DestTbl, or instead of storing the data in this format, you now have a query to get the desired result.

这些查询以以下格式放置数据:

These queries place the data in the format:

| 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) |