Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- 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
- 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)
- 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
- 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)
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement