SQL Server根据一列透视多个列

I have following source and destination tables in sql server 2008R2. How can I do pivot(s) in TSQL to get to the destination from source.

我在sql server 2008R2中有以下源表和目标表。如何在TSQL中执行pivot（s）以从源到达目标。

SourceTbl

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

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

Hoping that the empIndex will somehow help in the pivot.

希望empIndex以某种方式帮助支点。

2 个解决方案

#1

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:

由于您使用的是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;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

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:

如果你想使用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', convert(varchar(10), empenddate, 120)
) c (col, value);

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

见演示。一旦数据不透明，那么您可以应用PIVOT函数，因此最终代码将是：

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;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

Th above versions will work great if you have a limited number of empindex, but if not then you can use dynamic SQL:

如果您拥有有限数量的empindex，上述版本将会很有效，但如果没有，那么您可以使用动态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;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

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.

您可以使用这些查询来INSERT INTO您的DestTbl，或者不是以这种格式存储数据，您现在有一个查询来获得所需的结果。

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

#2

Wow this was more complicated than i imagined, but I did get it to work great! thanks. This was my final version. The TextKey contains the data you want to turn into columns, and the TextValue is the value that ends up inside each cell.

哇这比我想象的要复杂得多，但我确实让它变得更好！谢谢。这是我的最终版本。 TextKey包含要转换为列的数据，TextValue是在每个单元格内结束的值。

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)


select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(TextKey) 
                    from #SourceTbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT FromEntityID, DisplayName, ' + @cols + ' 
              FROM 
              (
                  select FromEntityID, DisplayName, TextKey, TextValue
                  from #SourceTbl
              ) x
              pivot 
              (
                  min(TextValue)
                  for TextKey in (' + @cols + ')
              ) p 
              ORDER BY FromEntityID
              '

execute(@query)