数据透视表中的数据类型转换错误

I have a database that stores our company's positions and "requirements". I.e. each position needs to have undergone a building induction, etc., etc. There's a program that allows the users to see/manage all this, but of course, someone wants an export for a client and it's not really possible with the current setup, I'm thinking a quick pivot table will get the job done though.

我有一个存储公司职位和“要求”的数据库。即每个位置都需要经历建筑物感应等等。有一个程序允许用户查看/管理所有这些,但当然,有人想要为客户端导出,而且当前设置无法实现,我想快速数据透视表可以完成工作。

I have the following tables;

我有以下表格;

---------------------------
|        Positions        |
---------------------------
| PositionID   | int      |
| PositionName | nvarchar |
---------------------------

------------------------------
|       Requirements         |
------------------------------
| RequirementID   | int      |
| RequirementName | nvarchar |
| RequirementType | bit      |
------------------------------

-------------------------
| Position Requirements |
-------------------------
| Position_ID    | int  |
| Requirement_ID | int  |
-------------------------

What I would like to do is pull out the data for a specific Position or Positions, i.e. SELECT * FROM Positions WHERE PositionName LIKE '%Manager%';
These Positions would form the leftmost column of the PivotTable.
For the top row of the PivotTable, I would like to have each RequirementName.
The internal data would be the RequirementType field (i.e. '0' or '1', maybe 'Any' / 'All').

我想要做的是提取特定位置或位置的数据,即SELECT * FROM Positions WHERE PositionName LIKE'%Manager%';这些位置将形成数据透视表的最左侧列。对于数据透视表的顶行,我想要每个RequirementName。内部数据将是RequirementType字段(即'0'或'1',可能是'Any'/'All')。

I've read and read and read, but I can never quite seem to get my head around the concept of them, so this is my current attempt;

我已阅读,阅读和阅读,但我似乎永远无法理解他们的概念,所以这是我目前的尝试;

SELECT *
FROM Requirements
PIVOT (MAX(RequirementType) FOR RequirementName IN ([Requirement], [Names], [Go], [Here])) AS pivtable
WHERE [Requirement], [Names], [Go], [Here] IN (
    SELECT RequirementName FROM Requirements WHERE RequirementID IN (
        SELECT Requirement_ID FROM PositionRequirements WHERE Position_ID IN (
            SELECT PositionID FROM Positions WHERE PositionName LIKE '%Manager%')));

1 个解决方案

#1

Your PIVOT query is not arranged properly. Try this one:

您的PIVOT查询排列不正确。试试这个:

SELECT * FROM
(
SELECT 
      PositionName,
      RequirementType,
      RequirementName 
FROM [Position Requirements] A 
LEFT JOIN Positions B ON A.Position_ID=B.PositionID 
LEFT JOIN Requirements C ON A.Requirement_ID=C.RequirementID
WHERE PositionName LIKE '%Manager%'
) AS TABLE
PIVOT(MAX(RequirementType) FOR RequirementName IN ([Requirement],[Names],[Go],[Here])AS pvt