`PIVOT` with `EXISTS`

Question

给定这样的表：

[Last Name] [First Name] [DepartmentID]
---------------------------------------
Doe         John         1
Doe         John         2
Black       Frank        3

，我想要这样的结果：

[Last Name] [First Name] [Accounting] [Management] [Development]
----------------------------------------------------------------
Doe         John         X            X
Black       Frank                                  X

到目前为止，我有一个这样的问题：

SELECT [Last Name], [First Name], [1], [2], [3]
FROM Employees
PIVOT(SUM(DepartmentID) FOR DepartmentID IN ([1], [2], [3])
GROUP BY [Last Name], [First Name], [1], [2], [3]

这给了我：

[Last Name] [First Name] [1] [2] [3]
----------------------------------------------------------------
Doe         John         1
Doe         John             2
Black       Frank                3

几个问题：

• 列名来自其他地方，即应该从函数或子查询派生。这能做到吗
• 我想我必须使用动态SQL来获得可能的部门，即我不能告诉PIVOT简单地确定所有的可能性
• 列值应仅为"X"（或类似值），而不包含实际值。每个可能的部门每个人不应该有多行，这意味着我不能按枢轴列进行分组。我如何获得每人一条线路

我真的不想也不需要聚合任何东西；我只是想显示每个部门的员工是否居住在该部门。PIVOT是在转移注意力吗？我可以用一种更复杂的方式来解决这个问题，每个部门都有一个EXISTS声明？

Answer 1

也许这会有所帮助：

首先一些测试数据：

CREATE TABLE tbl(LastName VARCHAR(100),FirstName VARCHAR(100),DepartmentID INT)
CREATE TABLE tblDepartment(DepartmentID INT,Name VARCHAR(100))
INSERT INTO tbl
SELECT 'Doe','John',1 UNION ALL
SELECT 'Doe','John',2 UNION ALL
SELECT 'Black','Frank',3
INSERT INTO tblDepartment
SELECT 1,'Accounting' UNION ALL
SELECT 2,'Management' UNION ALL
SELECT 3,'Development'

柱的凹入：

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                     QUOTENAME(Name))
FROM 
    tblDepartment
ORDER BY 
    Name

或者，您可能想将列与现有列连接起来，如下所示：

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                     QUOTENAME(Name))
FROM 
    tblDepartment
WHERE EXISTS
        (
            SELECT 
                NULL 
            FROM 
                tbl AS tbl2 
            WHERE 
                tblDepartment.DepartmentID=tbl2.DepartmentID) 
ORDER BY 
    Name

然后执行动态sql:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.LastName,
        tbl.FirstName,
        Department.Name,
        ''X'' as test
    FROM
        tbl AS tbl
        JOIN tblDepartment AS Department
            ON Department.DepartmentID=tbl.DepartmentID
)AS p
PIVOT
(
    MAX(test) FOR Name IN ('+@cols+')
) As Pvt'
EXECUTE(@query)

在我的案例中，丢弃创建的表：

DROP TABLE tbl
DROP TABLE tblDepartment