USE iMartADW
GO

DROP VIEW IF Exists extend.lkp_SchoolID
GO

CREATE VIEW extend.lkp_SchoolID AS
SELECT DISTINCT x.District_Id
    , x.School_Id
    , x.School_Unique_Id
FROM dim_School_Current x
GO


DROP VIEW IF EXISTS extend.District_ID_Security
GO

CREATE VIEW extend.District_ID_Security AS
SELECT DISTINCT sc2.District_Id,
    SUBSTRING(
        (
            SELECT sc1.school_unique_id + ',' AS [text()]
            FROM extend.lkp_SchoolID sc1
            WHERE sc1.District_ID = sc2.district_id
            ORDER BY sc1.district_id, sc1.School_Id
            FOR XML PATH ('')
        ), 1, 1000) [School_Id_Security_All]
FROM extend.lkp_SchoolID sc2
GO


Select * from extend.District_ID_Security
GO