在 SQL Server 2005 中透视两个或更多列

• 下载源代码 - 2.01 KB

引言

MSDN 声明你不能透视两列，这是正确的。如果你想要两列具有相同的列标题，那么除了手动处理或使用报表工具之外，你无法实现。

然而，通过稍作组织，你可以透视两列或更多列。在这篇小文章中，我将展示如何透视两个字段。我将从 Adventureworks 数据库中显示每个部门每年男女员工的招聘数量，如上图所示。

背景

开发人员经常会遇到工具（无论是语言、SQL 还是第三方工具）无法完成的需求。在我看来，开发人员的技能在于弯曲环境以满足需求。有时候，这是不可能的，但一个优秀的开发人员应该能够满足大多数业务需求。

大多数透视的例子都使用静态列标签；这在现实世界中很少，甚至从未出现过真正的解决方案。在我使用透视来解决需求的每一个案例中，我不得不使用动态列标题。这需要你使用动态 SQL，这是所有开发人员的噩梦。

通常，我将使用表变量来准备我的数据；但是，你不能将表变量传递给动态 SQL。但是，你可以引用临时表，这就是本例中使用的。

本代码片段中演示的一些 SQL

• 使用临时表将结果集放入动态 SQL
• 清理临时表
• 表变量
• Case 语句用于进行条件聚合
• 动态 SQL
• 在单个 Select 语句中连接行
• 如何构建透视选择

使用代码

你需要安装 Adventureworks 示例数据库。下载内容包括三个文件 pivot01/2/3。它们演示了代码片段的开发阶段。

构建透视语句时，需要在表中包含三种类型的信息。行轴，在本例中为部门标签，列轴，年份/性别标签，以及填充透视体的数据，在本例中为按招聘年份统计的男性/女性数量。

准备数据：我们需要获得一个包含以下结果的表。这通过 Pivot01.sql 实现。注意使用 CASE 语句仅在每个联合选择中聚合单个性别。

此外，我们需要区分每个年份/性别的列标签。

--Create a temp table because we cannot pass a table var into dynamic SQL
CREATE TABLE #TblX(Department VARCHAR(100),Staff INT, Yr VARCHAR(10))

--Insert the male records Note the case statement for the gender and the label creation
INSERT [#TblX]    
SELECT 
    D.Department,
    -- we only need the male staff in this column
    SUM(CASE E.Gender WHEN 'M' THEN 1 ELSE 0 END) Staff, 
    -- set the may year label
    CONVERT(CHAR(4),DATEPART(yy,E.HireDate)) + '-M' Yr 
FROM HumanResources.Employee E
    INNER JOIN HumanResources.vEmployeeDepartment D ON D.EmployeeID = E.EmployeeID
GROUP BY 
    D.Department,
    DATEPART(yy,E.HireDate)

UNION    
-- Now get the female staff, changing the label
SELECT 
    D.Department,
    SUM(CASE E.Gender WHEN 'F' THEN 1 ELSE 0 END) Staff,
    -- set the female year label
    CONVERT(CHAR(4),DATEPART(yy,E.HireDate)) + '-F' Yr 
FROM HumanResources.Employee E
    INNER JOIN HumanResources.vEmployeeDepartment D ON D.EmployeeID = E.EmployeeID
GROUP BY 
    D.Department,
    DATEPART(yy,E.HireDate)

组织列标签

这在 Pivot02.SQL 中完成。我们需要的是所有要透视的标签（年份/性别）的 CSV 字符串。这就是我使用表变量的原因之一，我不需要再次回到磁盘去获取列标签。

因为我不能使用 Select Distinct 和字符串连接，所以我将其拆分为两个操作并使用另一个表变量来获取不同的年份/性别列标签。

--We now need to build a CSV list of labels to use
DECLARE @TblY TABLE(Yr CHAR(6))
INSERT @TblY 
SELECT  DISTINCT Yr FROM [#TblX]
    
--Get the dynamic list of years (DISTINCT and the concatenation does not work)
SELECT @List = ISNULL(@List,'') + CASE 
    WHEN ISNULL(@List,'') = '' THEN '[' + yr + ']' 
    ELSE ',[' + Yr + ']' end
FROM @TblY
ORDER BY Yr

我们现在已准备好所有部分，可以将其组合成一个透视选择。

所以，提供数据的内部查询是

Select Department, Yr, Staff FROM #TblX F

我们需要用透视的开始和结束部分来包装它。开始部分提供列信息，因此是 Department 和 @List。结束部分提供聚合和透视。在这种情况下，聚合已经完成，所以我们只需使用 MAX() 来显示员工计数，告诉它要透视的列，并提供列值 - @List。

--Now to build the pivot query
Set @SQL = 'Select Department, ' + @List + char(13) 
--Inside query
Set @SQL = @SQL + 'From (Select Department, Yr, Staff ' + char(13) 
Set @SQL = @SQL + 'FROM #TblX F) P ' + char(13) 
--Pivot 
Set @SQL = @SQL + 'Pivot (Max(Staff) For Yr In ('  + @List + ')) as Pvt' + char(13) 


Print @SQL
Exec (@SQL)
DROP TABLE [#TblX]

关注点

我讨厌你不能在动态 SQL 中使用变量的陷阱。我发现连接技术非常有用。我只是希望我知道谁发明的它以便感谢他们。

添加要透视的附加列只是一个在临时表中组织标签和行的问题。例如，你可以转到销售部门，并按年份透视每个销售人员的销售额/目标/绩效。

当然，业务部门会回来抱怨他们不喜欢 NULL 并且希望将其替换为 0。这可以通过对年份/性别和部门进行交叉连接并让你的初始准备选择更新临时表而不是插入来实现。

历史

清理了一些格式。