MS SQL行转一列

MS SQL多行转列示例:


SELECT
  CASE
    WHEN DeptName='广州滨江东' THEN 'S49'
    WHEN DeptName='广州太古汇' THEN 'CS01'
    WHEN DeptName='广州天河城' THEN 'S48'
    WHEN DeptName='广州五月花' THEN 'CS05'
  END sh_code,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_id,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_name,
  AddTime sign_date,
  [1] as in_time1, [3] as in_time2, [5] as in_time3, [7] as in_time4,
  [2] as out_time1,[4] as out_time2,[6] as out_time3,[8] as out_time4,
  '' AS remark
FROM
(
  SELECT
    row_number() over (partition by CardID, CONVERT(VARCHAR(10), AddTime, 120) order by id) AS ID,
    CardID, EmployeeName, AttDateTime, DeptName,
    CONVERT(VARCHAR(10), AddTime, 120) AddTime
  FROM View_Staff_sgin
) AS SourceTable
PIVOT ( MAX(AttDateTime) FOR ID IN ([1],[2],[3],[4],[5],[6],[7],[8]) ) AS PivotTable
WHERE
  AddTime ='2015-03-21'

最主要的地方是用到了PIVOT语法,另外就是结合row_number()函数。

不过,确实为这个打卡表的设计感到郁闷!