CODEWITHSUNDEEP
sqlsql-serverjoin
slowlygettingthere
slowlygettingthere

Reputation: 117

Display count results of requests with results of jobs horizontally and locations vertically 3 tables

So I have a primary table of data which holds IDs against a job and location, so for example:

Requests Table dbo.Requests

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

Job Table dbo.Jobs

JobID    JobTitle
1        Job 1
2        Job 2
3        Job 3

Location Table dbo.Locations

LocationID   LocationName
1            Location 1
2            Location 2
3            Location 3

Now what I want to do is display a list of counts for jobs done and for locations, something like:

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

So I've got the first part fine for displaying the jobs and total count, but I'm stuck getting the locations to list to the right hand side?

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

Many thanks

Upvotes: 0

Views: 152

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

You can use the following query

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

And if you want use dynamic number of columns you can generate script and execute it using 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)

The variant with location titles

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)

Upvotes: 1

Related Questions