JM1 JM1 - 4 months ago 11
SQL Question

TSQL - How do you search a column containing a string using another column?

I am trying to isolate the school ID based on the most recent grade column. This shows which schools a student is zoned for. I'm not sure how to search a string based on a column result. What's the best way to isolate the schoolid?

Thank you.

Data:

enter image description here

Desired Result:

enter image description here

SQL Fiddle code: (I have trouble getting the schema to build without error.)

CREATE TABLE #T1 (
StudentID varchar(10),
AddressID varchar(10),
SchoolID varchar(10),
SchoolGrades varchar(100),
MostRecentGrade varchar(10),
)

INSERT INTO #T1
VALUES
('7777777','297','118','PK,KK,01,02,03,04,05,MG','10'),
('7777777','297','338','06,07,08,MG','10'),
('7777777','297','528','09,10,11,12,MG','10')

SELECT *
FROM #T1

Answer

First, you have a very poor data structure. You shouldn't be storing lists in a single column. You should have a separate table with one row for each grade in each school. Or, have max and min columns for the grades in a school -- assuming that schools have consecutive grades.

But, you can do what you want using like:

select t.*
from #t1 t
where ',' + t.schoolgrades + ',' like '%,' + mostrecentgrade + ',%';
Comments