T-SQL SELECT query to return combined result of mu

问题:

I am wondering if it is possible and more efficient to do something that I am presently doing in code, to do in T-SQL instead.

I have a database with courses. Each course can have different offerings which are variations of the course at different locations and at different awards.

Here's my (simplified) database structure and some sample data:

CREATE TABLE tblCourse (CourseId int, CourseName varchar(50))
CREATE TABLE tblOffering (OfferingId int, CourseId int, LocationId int, AwardId int)
CREATE TABLE tblLocation (LocationId int, LocationName varchar(50))
CREATE TABLE tblAward (AwardId int, AwardName varchar(50))

INSERT INTO tblCourse VALUES (1, 'Course A')
INSERT INTO tblCourse VALUES (2, 'Course B')

INSERT INTO tblOffering VALUES (1, 1, 1, 1)
INSERT INTO tblOffering VALUES (2, 1, 2, 1)
INSERT INTO tblOffering VALUES (3, 1, 3, 1)
INSERT INTO tblOffering VALUES (4, 1, 1, 2)
INSERT INTO tblOffering VALUES (5, 2, 3, 1)

INSERT INTO tblLocation VALUES (1, 'Location A')
INSERT INTO tblLocation VALUES (2, 'Location B')
INSERT INTO tblLocation VALUES (3, 'Location C')

INSERT INTO tblAward VALUES (1, 'Award A')
INSERT INTO tblAward VALUES (2, 'Award B')

What I want to retrieve from SQL is a single row for each course/award combination. Each row would have columns for each location and whether a course of that CourseId/AwardId combination was available. There would be now rows for course/award combinations that have no offerings.

The required result, from the sample data, would be a recordset like this:

CourseId | CourseName | AwardId | AwardName | LocationA | LocationB | LocationC
---------+------------+---------+-----------+-----------+-----------+----------
1        | Course A   | 1       | Award A   | True      | True      | True
1        | Course A   | 2       | Award B   | True      | NULL      | NULL
2        | Course B   | 1       | Award A   | NULL      | NULL      | True

(NULL could also be False)

At present I am doing a simple SELECT statement with various JOINS which gives me multiple rows for each course/award combination, then I loop through all rows in my code and build the required result. However, I don't think this is so efficient as I also need to page results.

I think I could do this fairly easily in a stored procedure by creating a temporary table and a bunch of separate queries, but I don't think that would be too efficient. Wondering if there is a better way of doing it in T-SQL???

So to clarify, what I am looking for is a T-SQL query or stored procedure that will produce the above sample recordset, and which I could adapt paging to.

NB. I am using SQL Server 2008

回答1:

For Dynamic columns:

DECLARE  @COLUMNS VARCHAR(max)
        ,@query varchar(1024)
        ,@True varchar(6)


SELECT @COLUMNS = 
COALESCE(
@Columns + ',[' + L.LocationName + ']',
'[' + L.LocationName +']'
)
FROM tblLocation L

SELECT @True = '''True'''

SELECT @QUERY = 'SELECT C.CourseName
                 ,A.AwardName
                 , pvt.*
FROM (SELECT O.OfferingID AS OID
            ,O.AwardID AS AID
            ,O.CourseID AS CID
            ,L.LocationName AS LID
       FROM tblOffering O Inner Join tblLocation L on L.LocationID = O.LocationID) AS S
PIVOT
(
    count(oID) For LID IN (' +@COLUMNS+ ')
) As pvt
inner join tblCourse C on C.CourseID = CID
inner join tblAward A on A.AwardID = pvt.AID'

EXEC (@QUERY)
GO

回答2:

This will produce a paginated version of your example results:

declare @tblCourse as table (CourseId int, CourseName varchar(50)) 
declare @tblOffering as table (OfferingId int, CourseId int, LocationId int, AwardId int) 
declare @tblLocation as table (LocationId int, LocationName varchar(50))
declare @tblAward as table (AwardId int, AwardName varchar(50)) 

INSERT INTO @tblCourse VALUES (1, 'Course A') 
INSERT INTO @tblCourse VALUES (2, 'Course B') 

INSERT INTO @tblOffering VALUES (1, 1, 1, 1) 
INSERT INTO @tblOffering VALUES (2, 1, 2, 1) 
INSERT INTO @tblOffering VALUES (3, 1, 3, 1) 
INSERT INTO @tblOffering VALUES (4, 1, 1, 2) 
INSERT INTO @tblOffering VALUES (5, 2, 3, 1) 

INSERT INTO @tblLocation VALUES (1, 'Location A') 
INSERT INTO @tblLocation VALUES (2, 'Location B') 
INSERT INTO @tblLocation VALUES (3, 'Location C') 

INSERT INTO @tblAward VALUES (1, 'Award A') 
INSERT INTO @tblAward VALUES (2, 'Award B') -- This had id 1 in your example.

-- Set the following parameters to control paging:
declare @PageSize as Int = 5
declare @PageNumber as Int = 1

; with CourseAwardSummary as (
  select distinct C.CourseId, C.CourseName, A.AwardId, A.AwardName,
    case when exists ( select 42 from @tblOffering where CourseId = C.CourseId and AwardId = A.AwardId and LocationId = 1 ) then 'True' end as LocationA,
    case when exists ( select 42 from @tblOffering where CourseId = C.CourseId and AwardId = A.AwardId and LocationId = 2 ) then 'True' end as LocationB,
    case when exists ( select 42 from @tblOffering where CourseId = C.CourseId and AwardId = A.AwardId and LocationId = 3 ) then 'True' end as LocationC
  from @tblCourse as C inner join
    @tblOffering as O on O.CourseId = C.CourseId inner join
    @tblAward as A on A.AwardId = O.AwardId
    ),
  CourseAwardSummaryRows as (
    select *, Row_Number() over ( order by CourseName, AwardName ) as RowNumber
      from CourseAwardSummary
    )
    select CourseId, CourseName, AwardId, AwardName, LocationA, LocationB, LocationC
  from CourseAwardSummaryRows
  where ( @PageNumber - 1 ) * @PageSize + 1 <= RowNumber and RowNumber <= @PageNumber * @PageSize
  order by CourseName, AwardName

回答3:

The following query does this by joining and aggregating the offering table, and then joining the result to the course and award tables:

select c.CourseId, c.CourseName, oa.AwardId, oa.AwardName,
       oa.LocationA, oa.LocationB, oa.LocationC
from tblCourse c left outer join
     (select o.CourseId, o.AwardId, a.awardName
             max(case when LocationName = 'Location A' then 'true' end) as LocationA,
             max(case when LocationName = 'Location B' then 'true' end) as LocationB,
             max(case when LocationName = 'Location C' then 'true' end) as LocationC
      from tblOffering o join
           tblLocation l
           on o.LocationId = l.LocationId join
           tblAward a
           on a.awardID = o.AwardId
      group by o.CourseId, o.AwardId, a.awardName
     ) oa
     on oa.CourseId = c.CourseId