使用来自多个表的单个hMatrix查询创建一个SQL视图

Question

我有三张桌子：tblEmpData，tblEmpAttDevice和tblEmpAttDeviceUsers。

tblEmpData拥有所有员工，tblEmpAttDevice拥有所有考勤设备，tblEmpAttDeviceUsers为特定设备id提供员工出勤。

表模式：

tblEmpData

    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmpID] [int] NOT NULL,
    [EmpName] [varchar](255) NOT NULL,
    [EmpCivilID] [varchar](100) NULL,
    [EmpCivilIDExp] [date] NULL,
    [EmpHourWageKD] [varchar](20) NULL,
    [EmpPhone] [varchar](50) NULL,
    [EmpDOB] [date] NULL,
    [EmpJoinDate] [date] NULL,
    [EmpType] [varchar](10) NULL,
    [EmpEmail] [varchar](255) NULL,
    [EmpAddress] [varchar](255) NULL,
    [EmpPassword] [varchar](255) NULL,
    [EmpEnabled] [int] NULL,
    [EmpDeviceID] [int] NULL,
    [EmpCompanyHired] [int] NOT NULL DEFAULT ((1)),
    [EmpVenueID] [int] NULL,
    [EmpStatusID] [int] NULL,
    [DriversLicenseNo] [varchar](100) NULL,
    [DriversLicenseExpiry] [date] NULL

tblEmpAttDevice

    [DeviceID] [int] NOT NULL,
    [DeviceName] [varchar](50) NULL,
    [DeviceIP] [varchar](50) NOT NULL,
    [DevicePort] [int] NOT NULL,
    [VenueID] [int] NOT NULL,
    [EventID] [int] NOT NULL,
    [LastImport] [datetime] NULL,
    [ACTIVE_FLAG] [bit] NULL

tblEmpAttDeviceUsers

    [EmpID] [int] NULL,
    [DeviceID] [int] NULL,
    [lastseen] [datetime] NULL,
    [action] [int] NULL

我希望生成SQL查询以生成以下视图。

​

​

tblEmpAttDevice表的所有行都变成列并创建矩阵，如果tblEmpAttDeviceUsers中存在任何雇员id，则Y else N。

我正处于阻塞阶段，我该怎么做呢？请帮助我在Server中构建一个查询。谢谢

Answer 1

在这种情况下，您可以使用PIVOT。

尝试使用下面的脚本。

SELECT EmpID,EmpName,Emp_Enabled,Employment_status
   ,CASE WHEN [Kuwait Soft]>0 THEN 'Y' ELSE 'N' END [Kuwait Soft]
   ,CASE WHEN [Avenues]>0 THEN 'Y' ELSE 'N' END [Avenues]
   ,CASE WHEN[360 Mall]>0 THEN 'Y' ELSE 'N' END [360 Mall]
   ,CASE WHEN [AlHarma]>0 THEN 'Y' ELSE 'N' END [AlHarma]
   ,CASE WHEN [Sidra]>0 THEN 'Y' ELSE 'N' END [Sidra]
   ,CASE WHEN[Symphony]>0 THEN 'Y' ELSE 'N' END [Symphony]

FROM
(SELECT a.EmpID, a.EmpID EmployeeID,a.EmpName
       ,a.EmpEnabled Emp_Enabled,a.EmpStatusID Employment_status,c.DeviceName
FROM tblEmpData a
 JOIN tblEmpAttDeviceUsers b on a.EmpID=b.EmpID
 JOIN tblEmpAttDevice c on b.DeviceID=c.DeviceID) src
 PIVOT (MAX([EmployeeID]) for [DeviceName]  IN ([Kuwait Soft],[Avenues],[360 Mall],[AlHarma],[Sidra],[Symphony]))as Res;

产出：

​

​