sql - `PIVOT` 与 `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