将InformationText拆分为两列，没有重复值或NULL值

Question

我试图从一个名为InformationText的列显示两个列。这一栏有我的网站内容和我的入门要求，它们由一个名为CourseInformationID的列分隔，WebContent为'1' &入职要求为"8"。我已将列拆分为两个子查询，但我收到错误Msg 512!我一直在寻找论坛和各种各样的试图找到一个没有运气的修复。下面是代码:

DECLARE 

@DAY VARCHAR (10) = ' Day(s)',
@Week VARCHAR (10) = ' Week(s)',
@Year VARCHAR (10) = ' Year(s)'




SELECT DISTINCT
COALESCE (O.QualID,'') QualID,

CASE WHEN LEN(O.Code) <= 9 THEN  LEFT (O.Code,5)
ELSE LEFT (O.Code,8)
END AS CourseCode,


CASE WHEN LEN(O.Code) <= 9 THEN SUBSTRING(O.Code,7,LEN(O.Code)-6)
ELSE SUBSTRING(O.Code,9,LEN(O.Code)-6) --Splits the Offering code into Course/Occurence code, Program/Course Levels. 
END AS OccurenceCode,



CASE 
WHEN (O.Name LIKE '%ACCESS%') THEN 'Adult - Access to Higher Education'

WHEN (O.Name LIKE '%ESOL%') THEN 'ESOL'

WHEN (O.Code LIKE '%-F2%') THEN 'Community Learning'

WHEN (CL.Name LIKE '%AAT%') OR (CL.Name LIKE '%BUSINESS%') THEN  'Business & Accounting'

WHEN (CL.Name LIKE '%Beauty%') OR (CL.Name LIKE '%Hair%') THEN 'Hair & Beauty'

WHEN (CL.Name LIKE '%Functional Skills%') OR (CL.Name LIKE '%GCSE%') THEN 'Maths & English'

WHEN (CL.Name LIKE '%Photography%') OR (CL.Name LIKE '%Graphics%') OR (CL.Name LIKE '%Media%') THEN 'Digital Media'

WHEN (CL.Name LIKE '%Sports%') THEN 'Sports'

WHEN (CL.Name LIKE '%Public Services%') THEN 'Public Services'

WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'

WHEN (CL.Name LIKE '%Animal Care%') THEN 'Animal Studies'

WHEN (CL.Name LIKE '%ICT%') THEN 'ICT Technology & Computing'

WHEN (CL.Name LIKE '%Childcare%' OR CL.Name LIKE '%Teaching%') THEN 'Teacher Education'

WHEN (CL.Name LIKE '%Hospitality & Catering%') OR (CL.Name LIKE '%Bus%')THEN 'Business & Accounting'  

WHEN (CL.Name LIKE '%Engineering%') OR (CL.Name LIKE '%Enviromental%') OR (CL.Name LIKE '%Sustainability%') OR (CL.Name LIKE '%LB Engineering Centre%') THEN 'Engineering' 

WHEN (CL.Name LIKE '%Construction%') THEN 'Construction' 

WHEN (CL.Name LIKE '%Motor Vehicle%') THEN 'Motor Vehicle' 

WHEN (CL.Name LIKE '%Science%') THEN 'Science'

WHEN (CL.Name LIKE '%Pathways%') THEN 'Pathways' 

WHEN (S.Description LIKE '%Distance Learning%') THEN 'Distance & Online Learning'

WHEN (S.Description LIKE '%Community%') OR (S.Description LIKE '%School%') OR (S.Description LIKE '%Farm%') OR (S.Description LIKE '%Stopsley%') THEN 'Community Learning'

-- HE COURSES(LO6)
WHEN (O.Name LIKE '%PCE%') OR (O.Name LIKE '%PGCE%') THEN 'Teacher Education'

WHEN (O.Name LIKE '%Child%') THEN 'ChildCare'

WHEN (O.Name LIKE '%Health%' OR O.Name LIKE 'Disability') THEN 'Health & Social Care'

WHEN (O.Name LIKE '%Leadership%' OR O.Name LIKE '%Management%') THEN 'Leadership & Management'

WHEN (O.Name LIKE '%SSS%') THEN 'SSS Birmingham & Manchester Based'

WHEN (O.Name LIKE '%Business%') THEN 'Business & Accounting'

WHEN (O.Name LIKE '%Computing%') THEN 'Technology & Computing'
END AS SubjectTab,   



vCLI.Level1Code LearningArea,
COALESCE (LA.NOTIONAL_NVQ_LEVEL_CODE, '') OfferingLevel, 
O.Name AS OfferingName,


(SELECT vCID.InformationText 
WHERE vCID.CourseInformationTypeID = '1') AS WebContent

,
(SELECT vCID.InformationText 
WHERE vCID.CourseInformationTypeID = '8') AS EntryRequirements 
,
vCID.CourseInformationTypeID,

AB.Name AwardingBody,

FORMAT(O.StartDate,'dd/MM/yyyy') AS StartDate ,
FORMAT(O.EndDate,'dd/MM/yyyy') AS EndDate,

CASE 
WHEN (O.NumberOfWeeks BETWEEN '35' AND '69') THEN CONCAT(1,@Year)
WHEN (O.NumberOfWeeks BETWEEN '70' AND '155') THEN CONCAT(2,@Year)
WHEN (O.NumberOfWeeks >= '156') THEN CONCAT(3,@Year)
WHEN (O.NumberOfWeeks BETWEEN '0' AND '34') THEN CONCAT(o.NumberOfWeeks,@Week)
WHEN DATEDIFF(DAY, O.StartDate, O.EndDate) BETWEEN '0' AND '7' THEN CONCAT(DATEDIFF(DAY, O.StartDate, O.EndDate),@DAY)
END AS Duration, 

S.Description AS Venue,

COALESCE (FMAF.DLF_Fee_LR_1618,0) AS Fee1618,FMAF.DLF_Fee_LR_Adult AS 'Fee19+','£' + COALESCE (CAST (vOS.Fee1 AS char(136)),'0.00') FullCost,

CASE 
WHEN LEN (O.Code) <=9 THEN '16-18'
WHEN (LA.NOTIONAL_NVQ_LEVEL_CODE >= '4') AND (vCLI.Level1Code = 'L06') THEN 'HE'
ELSE 'Adult'
END AS Category, 

--CITy.Description AS Content,
O.Code AS OfferingCode,
O.UserDefined1 AS Link

FROM
Offering O 

LEFT JOIN AwardingBody AB
ON O.AwardingBodyID = AB.AwardingBodyID

LEFT JOIN vOfferingStats vOS
ON O.OfferingID = vOS.OfferingID

LEFT JOIN vPR_FundMethodlogy_AssumedFee FMAF
ON O.OfferingID = FMAF.OfferingID

LEFT JOIN Site S
ON O.SiteID = S.SiteID 

INNER JOIN vCourseInformationDetail vCID
ON O.OfferingID = vCID.OfferingID

LEFT JOIN CollegeLevel CL 
ON O.SID = CL.SID 

LEFT JOIN vCollegeLevel_Info vCLI
ON  O.SID = vCLI.SID 

LEFT JOIN Learning_Aim LA
ON O.QualID = LA.LEARNING_AIM_REF 

WHERE 
WebSiteAvailabilityID = '2' 
AND GETDATE() <= DATEADD(DAY,42, O.StartDate) 
AND O.Name NOT LIKE '%Cancelled%' 
AND O.Name NOT LIKE '%School Link%' 
AND O.Name NOT LIKE '%Amazon%' 
AND O.Name NOT LIKE '%Apprentice%'
--AND O.Code NOT LIKE '%-%'
--AND O.Code NOT LIKE '%/%'

第一个样例表我现在得到了什么!

tbody> <<tr>

CousreCode	OccurenceCode	WebContent	EntryRequirements	CourseInformationTypeID	OfferingCode
2301	22	空	你应该4 GCSE成绩以或*	8	23201年22日
2301	22	类=在下="背景:……	空	1	23201年22日

left join (
SELECT 
vCID.InformationText, 
offeringid 
FROM 
vCourseInformationDetail vCID 
WHERE 
(
vCID.InformationText is not null 
AND vCID.CourseInformationTypeID = '1'
)
) as webcont on webcont.OfferingID = o.OfferingID 
left join (
SELECT 
vCID.InformationText, 
offeringid 
FROM 
vCourseInformationDetail vCID 
WHERE 
(
vCID.InformationText is not null 
AND vCID.CourseInformationTypeID = '8'
)
) as Entreq on entreq.OfferingID = o.OfferingID

解决