显示请求的计数结果，水平显示工作结果，垂直显示3个表的位置

一尘不染

显示请求的计数结果，水平显示工作结果，垂直显示3个表的位置

sql

因此，我有一个主要的数据表，其中包含针对工作和位置的ID，例如：

请求表dbo.Requests

RequestID    JobID    LocationID
1            3        5
2            4        8
3            3        2

作业表dbo.Jobs

JobID    JobTitle
1        Job 1
2        Job 2
3        Job 3

位置表dbo.Locations

LocationID   LocationName
1            Location 1
2            Location 2
3            Location 3

现在，我要做的是显示已完成工作和位置的计数列表，例如：

Job Title      Total Jobs    Location 1   Location 2  Location 3
Job 1          30            5            15          10
Job 2          10            2            2           6
Job 3          40            22           0           18

因此，我有一部分可以很好地显示职位和总人数，但我一直坚持将要列出的位置列在右侧？

SELECT 
 job.JobTitle
,isnull(COUNT(req.JobID),0) AS 'Total Jobs'

FROM  Requests req

RIGHT OUTER JOIN Jobs job
ON req.JobID = job.JobID

GROUP BY job.JobTitle
ORDER BY JobTitle

非常感谢

阅读 108

2021-03-17

共1个答案

一尘不染

您可以使用以下查询

SELECT JobID,JobTitle,[1],[2],[3],[4],[5],[6],[7],[8]
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN([1],[2],[3],[4],[5],[6],[7],[8])) p

如果要使用动态列数，则可以生成脚本并使用EXEC执行

DECLARE @locationIDs varchar(200)=''

SELECT @locationIDs+=CONCAT(',[',LocationID,']')
FROM Locations
ORDER BY LocationID

SET @locationIDs=STUFF(@locationIDs,1,1,'')

--PRINT @locationIDs

DECLARE @query varchar(1000)=CONCAT('SELECT JobID,JobTitle,',@locationIDs,'
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN(',@locationIDs,')) p')

--PRINT @query

EXEC(@query)

具有位置标题的变体

DECLARE
  @locationIDs varchar(200)='',
  @locationTitles varchar(2000)=''

SELECT
  @locationIDs+=CONCAT(',[',LocationID,']'),
  @locationTitles+=CONCAT(',[',LocationID,'] [',LocationName,']')
FROM Locations
ORDER BY LocationID

SET @locationIDs=STUFF(@locationIDs,1,1,'')
SET @locationTitles=STUFF(@locationTitles,1,1,'')

--PRINT @locationIDs
--PRINT @locationTitles

DECLARE @query varchar(2000)=CONCAT('SELECT JobID,JobTitle,',@locationTitles,'
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN(',@locationIDs,')) p')

--PRINT @query

EXEC(@query)

2021-03-17