在SQLServer2005中水平选择行

Question

我有员工SQL表（SQL Sever 2005）：

EmployeeID Field1 Field2
121212     MISC1  ABC
121212     MISC2  XYZ
121213     MISC1  AAA
121213     MISC2  BBB

我想显示这样的结果：

EmployeeID MISC1 MISC2
121212     ABC   XYZ
121213     AAA   BBB

请提前告知并感谢您的帮助。

Answer 1

您可以使用条件聚合来完成此操作

SELECT
    EmployeeID,
    MISC1 = MAX(CASE WHEN Field1 = 'MISC1' THEN Field2 END),
    MISC2 = MAX(CASE WHEN Field1 = 'MISC2' THEN Field2 END)
FROM tbl
GROUP BY EmployeeID 

如果你想动态地做：

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = 
'SELECT
    EmployeeID' + CHAR(10) +
(SELECT
'   , MAX(CASE WHEN Field1 = ''' + Field1 +''' THEN Field2 END) AS ' + QUOTENAME(Field1) + CHAR(10)
FROM (
    SELECT DISTINCT Field1 FROM tbl
)t
FOR XML PATH('')
) +
'FROM tbl
GROUP BY EmployeeID;';
EXEC(@sql);

参考：Jeff Moden 的Cross Tabs and Pivots