Cross Apply using XML Path(comma delineated) - Remove Max Row if > 1

Question

What I want is if it returns more than 1 then remove the MAX([year]) Row but return the rest. Meaning if Cross Applyselect returns 4 records return only 3. If 3 then return only 2. If 2 it returns only 1. I believe the best way is to change the comma to another character (like #). Right search for # delete everything Right following. If I didn't use Cross Apply I would could have 3 rows Row(1)18 ----John Smith ---- BA ---- College1 --- 1990 Row(2) 18----John Smith ---MS---- Univ1------2003 Row(3)18----John Smith ----BA ----Univ2 -----2010.... I want to remove Row (3) because it is the students latest/current degree information. I only want the Prior degree(s), institution(s), year(s) not the current. Maybe a Right(rtrim(everthing after last comma) but is has to count Below is the SQL:

SELECT 
DISTINCT 
                      vw_name.personID AS personID,
                       vw_name.FullName AS FullName,
                        (CASE WHEN LEN(commaDegree) <= 2 THEN commaDegree ELSE LEFT(commaDegree, 
                      LEN(commaDegree) - 1) END) AS commaDegree,
                       (CASE WHEN LEN(commaInst) <= 2 THEN commaInst ELSE LEFT(commaInst, LEN(commaInst) - 1) END) 
                      AS commaInst,
                       (CASE WHEN LEN(commaYear) <= 2 THEN commaYear ELSE LEFT(commaYear, LEN(commaYear) - 1) END) 
                      AS commaYear
FROM vw_name LEFT JOIN
                      vw_deg_all ON 
                      vw_deg_all.personID = vw_name.personID                      

CROSS
                       APPLY
                          (SELECT     isnull(degree.degree + N', ', N'')
                            FROM          vw_deg_all LEFT JOIN
                                                   degree ON vw_deg_all.degreeID = degree.id
                            WHERE      vw_deg_all.personID = vw_name.personID
                            ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS t (commaDegree) 


CROSS APPLY
                          (SELECT     isnull(institution.inst + N', ', N'')
                            FROM          vw_deg_all LEFT JOIN
                                                   institution ON vw_deg_all.institutionID = institution.id
                            WHERE      vw_deg_all.personID = vw_name.personID
                            ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS u(commaInst) 

CROSS APPLY
                          (SELECT     isnull(CONVERT(nvarchar, vw_deg_all.[year]) + N', ', N'')
                            FROM          vw_deg_all
                            WHERE      vw_deg_all.personID = vw_name.personID
                            ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS v(commaYear)

Resulting Query

PersonID -- FullName -- Comma Degree -- CommInst -- CommYear

18 -- John Smith -- BA, MS, PHd -- College1, Univ1, Univ2 -- 1990, 2003,2010

45 -- Paul Ryan -- BA, MS -- College15, Univ19 -- 1999, 2008

134 -- Cindy Jones -- BA -- College56 -- 1978

I only want to return the PRIOR degree(s), Institution(s), Year(s). Not the latest . Meaning PersonID 18 remove PHd, Univ2, 2010. PersonID 45 remove MS, Univ19, 2008. PersonID 134 leave as is since only 1 record.

Hope this makes sense. Thanks in advance.

score 0 · Answer 1 · answered Feb 20 '13 at 18:58

0

If I understand well you want to remove the first value of each CSV column. Why not do it like this :

DECLARE @Field as nvarchar(MAX)
SET @Field = 'College1, Univ1, Univ2'
SELECT Substring(@Field,PATINDEX('%,%',@Field)+1,LEN(@Field)-PATINDEX('%,%',@Field))

Hope this helps!

answered Feb 20 '13 at 18:58

HucKQC

61
9

Hello, If you look at the resulting query at the bottom. If I didn't use a Cross Apply the I would have 3 rows for PersonID 18: – sjackson5280 Feb 20 '13 at 19:05
Row(1)18 ----John Smith ---- BA ---- College1 --- 1990 Row(2) 18----John Smith ---MS---- Univ1------2003 Row(3)18----John Smith ----BA ----Univ2 -----2010.... I want to remove Row (3) because it is the students latest/current degree information. I only want the Prior degree(s), institution(s), year(s) not the current. Maybe a Right(rtrim(everthing after last comma) but is has to count... – sjackson5280 Feb 20 '13 at 19:50
What if you add this:`vw_deg_all LEFT JOIN institution ON vw_deg_all.institutionID = institution.id and vw_deg_all.[year] < Max(vw_deg_all.[year])` – HucKQC Feb 20 '13 at 20:38
Thanks for the reply.. It is something like this but the cross apply xml path is where it needs to go... I think?? If not what does the sql look like? – sjackson5280 Feb 20 '13 at 21:27

Cross Apply using XML Path(comma delineated) - Remove Max Row if > 1

1 Answers1