Iterating a SELECT statement in SQL Server 2008 using a WHILE LOOP

Question

I have a database that gives an employeeID, a job, an effectiveDate, and a dept. If an employee has worked more than one job they will have an additional row of data. My goal is to compress the rows corresponding to each employee into one. Basically I need a query to that pulls from a db that looks like this:

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

To output like this:

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

So far I have done this:

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

And that works just fine. The problem is that I need to make many columns, and I do not want to write all that code 20 times. So I want to iterate through using a WHILE command. Here is what I tried in that second SELECT statement:

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

I knew this probably wouldn't work because SQL will not understand the naming conventions I am trying to call each table and field. Is there a way using a cast or a concat command that I can automate the process so it just increments the numbers where I am asking it to?

Answer 1

Here is the solution.No matter how many job changes for the Emp it will pivot all of them If you want to Pivot only 20 then set @MAXCol =20

edit: forget parentheses around @SQL in last line

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


DECLARE @MAXCol INT = (SELECT MAX(RowNum)FROM #temp1)
 ,@index INT =1
 ,@ColNames varchar(4000)=''
 ,@SQL VARCHAR(MAX)=''
  WHILE (@index<=@MAXCol)
  BEGIN 
     SET @ColNames =@ColNames +'MAX(CASE WHEN RowNum = '+LTRIM(STR(@index))+' THEN Job END) as Job'+LTRIM(STR(@index))+','
                              +'MAX(CASE WHEN RowNum = '+LTRIM(STR(@index))+' THEN EffDate END) as EffDate'+LTRIM(STR(@index))+','
                              +'MAX(CASE WHEN RowNum = '+LTRIM(STR(@index))+' THEN Dept END) as Dept'+LTRIM(STR(@index))+','
     SET @Index=@Index +1        
 END
SET @ColNames = LEFT(@ColNames,LEN(@ColNames)-1) -- Remove Last Comma

SET @SQL =  'SELECT EmpNo ,'+@ColNames+' FROM #temp1 GROUP BY EmpNo'

EXECUTE (@SQL)

Here is SQL Fiddle Demo working

http://sqlfiddle.com/#!3/99cea/1

Answer 2

First, let me maske clear that this is not directly an answer to the question. However, due to the large code block it is not suitable for a comment either, and I feel that it does add value to the question. So here it goes...

Having a dynamic number of columns is rarely a good solution. I'd opt for a different solution if using XML is an option:

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

Answer 3

You can do an UNPIVOT and then a PIVOT of the data. this can be done either statically or dynamically:

Static Version:

select *
from 
(
  select empid, col + cast(rn as varchar(10)) colname, value
  from
  (
    select Top 20 empid,
      job,
      convert(varchar(10), effdate, 101) effdate,
      dept,
      row_number() over(partition by empid order by effdate) rn
    from yourtable
    order by empid
  ) x
  unpivot
  (
    value
    for col in (Job, Effdate, Dept)
  ) u
) x1
pivot
(
  min(value)
  for colname in([Job1], [EffDate1], [Dept1],
                 [Job2], [EffDate2], [Dept2],
                 [Job3], [EffDate3], [Dept3])
)p

see SQL Fiddle with Demo

Dynamic Version:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @colsPivotName as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+ quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable') and
               C.name not in ('empid')
         for xml path('')), 1, 1, '')

select @colsPivot 
  = STUFF((SELECT  ',' 
             + quotename(c.name + cast(t.rn as varchar(10)))
           from 
           (
             select row_number() over(partition by empid order by effdate) rn
             from yourtable
           ) t
           cross apply sys.columns as C
           where C.object_id = object_id('yourtable') and
                C.name not in ('empid')
           group by c.name, t.rn
           order by t.rn, c.name desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
        select empid, col + cast(rn as varchar(10)) colname, value
        from
        (
          select Top 20 empid,
            job,
            convert(varchar(10), effdate, 101) effdate,
            dept,
            row_number() over(partition by empid order by effdate) rn
          from yourtable
          order by empid
        ) x
        unpivot
        (
          value
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        min(value)
        for colname in ('+ @colspivot +')
      ) p'

exec(@query)

see SQL Fiddle with Demo

Answer 4

Here's one way using a series of dynamically-created MAX/CASE expressions. You could also do this with PIVOT but this is quicker for me:

DECLARE @sql NVARCHAR(MAX) = N'SELECT EmpID';

SELECT TOP (20) @sql += N', 
  Job'     + rn + ' = MAX(CASE WHEN rn = ' + rn + ' THEN Job END), 
  EffDate' + rn + ' = MAX(CASE WHEN rn = ' + rn + ' THEN EffDate END), 
  Dept'    + rn + ' = MAX(CASE WHEN rn = ' + rn + ' THEN Dept END)'
FROM 
(
  SELECT rn = RTRIM(ROW_NUMBER() OVER (ORDER BY name)) 
  FROM sys.all_objects
) AS x;

SET @sql += ' FROM (SELECT *, rn = ROW_NUMBER() OVER 
  (PARTITION BY EmpID ORDER BY EffDate) FROM dbo.your_table) AS y
GROUP BY EmpID;';

EXEC sp_executesql @sql;

You can probably tune this so that it determines the maximum number of job changes for any employee, rather than just defaulting to 20. You might also consider ordering the opposite way - surely an employee's last 20 job changes are more relevant than their first 20, if they've had more than 20.