CREATE VIEW MyView
AS
SELECT
  [Student Number],
  IRN,
  Name,
  Gender,
  Address,
  city,
  state,
  zip,
  phone,
  birthdate,
  Grade,
  Lastname4 + first AS 'Homeroom',
  Teacher
FROM   (SELECT
          DISTINCT
          LEFT(ISNULL(s.studentNumber, '') + SPACE(9), 9)                                   AS 'Student Number',
          CASE LEFT (ISNULL (sc.number, '') + SPACE (6), 6)
            WHEN 5101
            THEN '029876'
            WHEN 5102
            THEN '145086'
            WHEN 5111
            THEN '112581'
            WHEN 5112
            THEN '145078'
            WHEN 5121
            THEN '138966'
            WHEN 5122
            THEN '138958'
            WHEN 5123
            THEN '011369'
            WHEN 5131
            THEN '086579'
            WHEN 5135
            THEN '142638'
            WHEN 5132
            THEN '029868'
            WHEN 5133
            THEN '065433'
            WHEN 5137
            THEN '011368'
            WHEN 5136
            THEN '011370'
            WHEN 5134
            THEN '123307'
          END                                                                               AS 'IRN',
          LEFT(ISNULL(s.lastName + ', ' + s.firstName, '') + SPACE(36), 36)                 AS 'Name',
          LEFT(ISNULL(s.gender, '') + SPACE(1), 1)                                          AS 'Gender',
          LEFT(ISNULL(a.number + ' ' + a.street + ' ' + a.tag, '') + SPACE(51), 51)         AS 'Address',
          LEFT(ISNULL(a.city, '') + SPACE(18), 18)                                          AS 'City',
          LEFT(ISNULL(a.state, '') + SPACE(4), 4)                                           AS 'State',
          LEFT(ISNULL(a.zip, '') + SPACE(9), 9)                                             AS 'Zip',
          LEFT(ISNULL(hh.phone, '') + SPACE(14), 14)                                        AS 'Phone',
          CONVERT(VARCHAR, s.birthdate, 111)                                                AS 'birthDate',
          LEFT(ISNULL(s.grade, '') + SPACE(6), 6)                                           AS 'Grade',
          LEFT(ISNULL (se.teacherdisplay, '') + SPACE (4), 4)                               AS Lastname4,
          LEFT(LTRIM(STUFF(se.teacherdisplay, 1, CHARINDEX(',', se.teacherdisplay), '')), 1)AS 'First',
          LEFT(ISNULL(se.teacherdisplay, '') + SPACE(26), 26)                               AS 'Teacher',
          ROW_NUMBER() OVER(PARTITION BY s.studentnumber ORDER BY s.studentnumber)          AS RN
        FROM   pickerington.dbo.student s
               INNER JOIN pickerington.dbo.school sc
                 ON sc.schoolID = s.schoolID
               LEFT JOIN pickerington.dbo.HouseholdMember hm
                 ON hm.personID = s.personID
               LEFT JOIN pickerington.dbo.Household hh
                 ON hh.householdID = hm.householdID
               LEFT JOIN pickerington.dbo.HouseholdLocation hl
                 ON hl.householdID = hh.householdID
               LEFT JOIN pickerington.dbo.Address a
                 ON a.addressID = hl.addressID
               JOIN pickerington.dbo.Roster r
                 ON r.personID = s.personID
               JOIN pickerington.dbo.Section se
                 ON se.sectionID = r.sectionID
               JOIN pickerington.dbo.SectionPlacement sp
                 ON sp.sectionID = se.sectionID
               JOIN pickerington.dbo.Period p
                 ON p.periodID = sp.periodID
               JOIN pickerington.dbo.Course c
                 ON c.courseID = se.courseID
               JOIN pickerington.dbo.Calendar ca
                 ON ca.calendarID = c.calendarID
                    AND ca.calendarID = s.calendarID
                    AND sc.schoolID = ca.schoolID
               JOIN pickerington.dbo.SchoolYear sy
                 ON sy.endYear = ca.endYear
        WHERE  s.calendarID IN ( 8, 12, 16, 20,
                                 24, 28, 32, 36,
                                 40, 44, 48, 52,
                                 56, 60 )
           AND s.startYear = '2012'
           AND s.endDate IS NULL
           AND (( ( sc.number IN ( 5101, 5102, 5111, 5112 )
                    AND p.seq = 2 )
                   OR ( sc.number IN ( 5121, 5122, 5123, 5131,
                                       5132, 5133, 5134, 5135,
                                       5136, 5137 )
                        AND c.homeroom = '1'
                         OR p.seq = 1 ) )))s
WHERE  rn = 1 

bcp "SELECT * FROM DatabaseName.dbo.MyView" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T

 WHERE  s.calendarID IN ( 8, 12, 16, 20,
                                 24, 28, 32, 36,
                                 40, 44, 48, 52,
                                 56, 60 )