基于 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) |
相关文章