基于 SQL Server 中的一列透视多列 [英] Pivot multiple columns based on one column in SQL Server

问题描述

我在 SQL Server 2008R2 中有以下源表和目标表.如何在 TSQL 中进行数据透视以将 SourceTbl 转换为 DestTbl?希望 empIndex 能以某种方式在支点上有所帮助.

I have the following source and destination tables in SQL Server 2008R2. How can I do pivot(s) in TSQL to transform SourceTbl into DestTbl? Hoping that the empIndex will somehow help in the pivot.

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

推荐答案

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

这篇关于基于 SQL Server 中的一列透视多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！