我需要转换以下数据：
EmployeeId DataCategory DataValue StartDate
---------- ------------ ---------- ----------
2 'OfficeCode' 121 '03-01-12'
2 'ManagerName' 'Steven' '02-04-12'
2 'State' 'MA' '04-05-12'
5 'OfficeCode' 133 '04-01-12'
5 'ManagerName' 'Marcus' '05-04-12'
5 'State' 'WA' '01-05-12'
6 'ManagerName' 'Steven' '07-04-12'
6 'State' 'RI' '06-05-12'
7 'State' 'CA' '08-08-12'
到：
EmployeeId OfficeCode ManagerName State OfficeCodeStartDate
---------- ------------ ---------- ---------- -------------------
2 121 'Steven' 'MA' '03-01-12'
5 133 'Marcus' 'WA' '04-01-12'
6 null 'Steven' 'RI' null
7 null null 'CA' null
我可以这样枢纽：
select EmployeeId, OfficeCode, ManagerName, State
from
(
select EmployeeId, DataCategory, DataValue
from emp
) src
pivot
(
max(DataValue)
for DataCategory in (OfficeCode, ManagerName, State)
) piv
但是，我还需要DataCategory OfficeCode的StartDate（忽略任何其他类别的开始日期）。您可以使用枢轴/不枢轴帮助我达到预期的结果吗？除非需要，否则我试图避免加入/联合。
三种方式：一种使用UNION，一种使用JOIN，一种不使用PIVOT。选择最喜欢的一个（根据查询计划，最后一个是最快的）。
/*
CREATE TABLE emp (
EmployeeId INT,
DataCategory VARCHAR(50),
PRIMARY KEY (EmployeeID, DataCategory),
DataValue VARCHAR(50) NOT NULL,
StartDate VARCHAR(10) NOT NULL
)
INSERT INTO emp VALUES
('2','OfficeCode','121','03-01-12'),
('2','ManagerName','Steven','02-04-12'),
('2','State','MA','04-05-12'),
('5','OfficeCode','133','04-01-12'),
('5','ManagerName','Marcus','05-04-12'),
('5','State','WA','01-05-12'),
('6','ManagerName','Steven','07-04-12'),
('6','State','RI','06-05-12'),
('7','State','CA','08-08-12')
*/
select EmployeeId, OfficeCode, ManagerName, State, OfficeCodeStartDate
from
(
select EmployeeId, DataCategory, DataValue
from emp
union all
select EmployeeId, 'OfficeCodeStartDate' AS DataCategory, StartDate AS DataValue
from emp
WHERE DataCategory='OfficeCode'
) src
pivot
(
max(DataValue)
for DataCategory in (OfficeCode, ManagerName, State, OfficeCodeStartDate)
) piv
select piv.EmployeeId, OfficeCode, ManagerName, State, emp.StartDate AS OfficeCodeStartDate
from
(
select EmployeeId, DataCategory, DataValue
from emp
) src
pivot
(
max(DataValue)
for DataCategory in (OfficeCode, ManagerName, State)
) piv
LEFT JOIN emp ON emp.EmployeeId=piv.EmployeeId AND emp.DataCategory='OfficeCode'
SELECT EmployeeId,
MIN(CASE WHEN DataCategory='OfficeCode' THEN DataValue END) AS OfficeCode,
MIN(CASE WHEN DataCategory='ManagerName' THEN DataValue END) AS ManagerName,
MIN(CASE WHEN DataCategory='State' THEN DataValue END) AS State,
MIN(CASE WHEN DataCategory='OfficeCode' THEN StartDate END) AS OfficeCodeStartDate
FROM emp
GROUP BY EmployeeId
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句