sql - 使用 WHILE LOOP 在 SQL Server 2008 中迭代 SELECT 语句

Question

我有一个数据库，它提供了一个员工 ID、一份工作、一个有效日期和一个部门。如果员工从事过一项以上的工作，他们将获得额外的数据行。我的目标是将与每个员工对应的行压缩为一个。基本上我需要一个查询来从一个看起来像这样的数据库中提取:

EmpID   Job    EffDate       Dept
001     QB     01-01-2001    OFF
001     LB     01-01-2010    DEF
001     K      01-01-2005    SPEC
002     HC     01-01-2007    STAFF
003     P      01-01-2001    SPEC
003     CB     01-01-2002    DEF

要像这样输出:

EmpID   Job1  EffDate1    Dept1  Job2  EffDate2    Dept2  Job3  EffDate3    Dept3  
001     QB    01-01-2001  OFF    K     01-01-2005  SPEC   LB    01-01-2010  DEF
002     HC    01-01-2007  STAFF  
003     P     01-01-2001  SPEC   CB    01-01-2002  DEF

到目前为止，我已经这样做了:

SELECT
EmpNo
, Job
, EffDate
, Dept
, ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY EffDate) AS RowNum
INTO #temp1
FROM JobHist
ORDER BY EffDate DESC

SELECT
JobHist.EmpNo
, JobHist.Job AS Job1
, JobHist.EjhJobDesc AS JobDesc1
, JobHist.EffDate AS EffDate1
, JobHist.Dept AS Dept1
, temp2.Job AS Job2
, temp2.EffDate AS EffDate2
, temp2.Dept AS Dept2
FROM #temp1 AS JobHist LEFT JOIN #temp1 AS temp2 ON JobHist.EmpNo = temp2.EmpNo AND temp2.RowNum = 2
WHERE JobHist.RowNum = 1

这很好用。问题是我需要写很多列，而且我不想写所有代码 20 次。所以我想使用 WHILE 命令进行迭代。这是我在第二个 SELECT 语句中尝试的内容:

DECLARE @Flag INT
DECLARE @FlagPlus INT
SET @Flag = 1
SET @FlagPlus = (@Flag + 1) 
WHILE(@Flag < 20)
BEGIN
SELECT
temp@Flag.EmpNo
, temp@Flag.Job AS Job@Flag
, temp@Flag.EjhJobDesc AS JobDesc@Flag
, temp@Flag.EffDate AS EffDate@Flag
, temp@Flag.Dept AS Dept@Flag
FROM #temp1 AS temp@Flag
LEFT JOIN #temp@Flag AS temp@FlagPlus
ON temp@Flag.EmpNo = temp@FlagPlus.EmpNo AND temp@FlagPlus.RowNum = @FlagPlus
WHERE JobHist.RowNum = 1
SET @Flag = (@Flag + 1)
SET @FlagPlus = (@FlagPlus + 1)
END

我知道这可能行不通，因为 SQL 不会理解我试图调用每个表和字段的命名约定。有没有一种使用 cast 或 concat 命令的方法可以使该过程自动化，因此它只会增加我要求的数字？

Answer 1

首先，让我明确一点，这不是问题的直接答案。但是，由于代码块很大，它也不适合发表评论，我觉得它确实为问题增加了值(value)。所以这里...

拥有动态列数很少是一个好的解决方案。如果可以选择使用 XML，我会选择不同的解决方案:

SELECT
  e.EmpNo,
  (SELECT
      h.Job,
      h.EffDate,
      h.Dept
    FROM JobHist h 
    WHERE e.EmpNo = h.EmpNo 
    ORDER BY EffDate DESC 
    FOR XML PATH('job'), ROOT('jobs'), TYPE
  ) Jobs
  FROM (SELECT DISTINCT EmpNo FROM JobHist) e