更新时间:2022-03-10 22:06:58
对于动态列:
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