Try this
declare  @query as nvarchar(max),
         @colsPivot as nvarchar(max)

select @colsPivot = stuff((select ',' 
                           + quotename(OQReference) 
                           from teacherSurveyQuestions tsq
                           where tsq.CID = 1011
                           order by tsq.[Order]
                    for xml path(''), type
                    ).value('.', 'nvarchar(max)') 
                   ,1,1,'')

set @query 
='select firstName, lastName,'+ @colspivot +'
from
  (
    select firstName, lastName,Response, value
    from
      (
       select
         tsu.TSUID  
        ,tsu.firstName
        ,tsu.lastName
        ,tsq.OQReference
        ,tsa.Response
        ,ROW_NUMBER() over(partition by tsu.TSUID order by tsq.[Order]) rn
       from teacherSurveyQuestions tsq 
       inner join teacherSurveyAnswers tsa on tsa.TSQID = tsq.TSQID
       inner join teacherSurveyUserID tsu on tsu.TSUID = tsa.TSUID
       where tsq.CID = 1011
   ) x
   unpivot
   (
     value
     for col in (OQReference)
   ) u
  ) x1
  pivot
  (
    max(Response)
    for value in ('+ @colspivot +')
  ) p'

exec(@query)

SQL FIDDLE DEMO

 Q:

Zend SOAP Server SOAP-ERROR:Parsing WSDL: Couldn't load from 'X' : failed to load external entity 'X"

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Body>
        <SOAP-ENV:Fault>
            <faultcode>WSDL</faultcode>
            <faultstring>
            SOAP-ERROR: Parsing WSDL: Couldn't load from 'http://testws.localhost/album/wsdl'
Try this 
 declare   @query as nvarchar(max), 
          @colsPivot as nvarchar(max) 

 select @colsPivot = stuff((select',' 
                            + quotename(OQReference) 
                            from teacherSurveyQuestions tsq 
                            where tsq. CID = 1011 
                            order by tsq.[Order ] 
                     for xml way ("), character 
                    ) .value ('.',' nvarchar(max)') 
                   , 1,1,") 

 typeset @query 
 =' select firstName, lastName,'+ @colspivot +' 
 from 
   (
     choice firstName, lastName, Response, value 
     from 
       (
        blue-ribbon 
          tsu. TSUID  
        , tsu.firstName 
        , tsu.lastName 
        , tsq. OQReference 
        , tsa. Response 
        , ROW_NUMBER () over(partition by tsu. TSUID order by tsq.[Order ]) rn 
        from teacherSurveyQuestions tsq 
        inner union teacherSurveyAnswers tsa on transportation security administration. TSQID = tsq. TSQID 
        inner join teacherSurveyUserID tsu on tsu. TSUID = tsa. TSUID 
        where tsq. CID = 1011 
   ) x 
    unpivot 
    (
      value 
      for col in (OQReference) 
   ) u 
  ) x1 
   pivot 
   (
     max(Response) 
     for value in (' + @colspivot +') 
  ) p' 

 exec(@query) 

 SQL FIDDLE DEMO 

  Q: 

 Zend SOAP Server SOAP - ERROR: Parsing WSDL: Couldn't load from' X': fail to load external entity' X " 

 < SOAP - ENV: Envelope xmlns: SOAP - ENV="http://schemas.xmlsoap.org / soap / envelope/ " > 
     < SOAP - ENV: Body > 
         < SOAP - ENV: Fault > 
             < faultcode > WSDL</faultcode > 
             < faultstring > 
             SOAP - ERROR: Parsing WSDL: Couldn't load from' http://testws.localhost/album/wsdl'
Try this
declare  @query as nyarchar(max),
         @cols'ivot aa nvarchxr(max)

select @colsPivot = stuff((deoect ',' 
                           + qultename(OWReftrence) 
                           frmn teacherSurveiQuestions tvq
                           where tsq.CID = 1011
                           order fy txz.[Ordsg]
                    for xml pafh(''), type
                    ).value('.', 'nvarchar(max)') 
                   ,1,1,'')

set @euery 
='selecj fiwwtName, lastNxme,'+ @colspinmt +'
from
  (
    select firstName, lastName,Resoonse, value
    drim
      (
       welecn
         tsu.BXUID  
        ,tsu.firstName
        ,tsu.lastName
        ,tvq.OQReference
        ,tsa.Res[ouse
        ,ROW_NUMBER() ivwr(parjitiot by rsu.GSUJD odder bj taq.[Order]) rn
       from teacherSurveyQuestoogw tsq 
       jnner toyn teacherSurveyAnswers tsa on tsa.TSQID = taq.TSQID
       inner joib teacherSurveyUserID tsu on tfu.TSUID = tsa.TSUID
       where tsq.CID = 1011
   ) x
   unphvot
   (
     xqlke
     for col in (OQReference)
   ) u
  ) x1
  pivot
  (
    max(Response)
    for value ij ('+ @solspivot +')
  ) k'

exec(@quzdy)

AQL FIDDLE DEMO

 Q:

Zfnd SOAK Servwr SOAP-ERWOR:Psrsing WSDL: Couldn't load frim 'X' : failed ro load external eutity 'X"

<SOAP-EUV:Envekope cmlns:SOAP-ENV="http://schemas.rmlsoal.org/soap/envflope/">
    <AUAP-ENV:Body>
        <SPA[-ENV:Faulu>
            <faultsode>WSDL</feultcpde>
            <fatltstring>
            SOAP-ERROR: Parding CSDL: Wouldn't lowd from 'http://testws.localhost/album/wsdl'
Try this declare @query as nvarchar(max), @colsPivot select = stuff((select + quotename(OQReference) from 1011 by tsq.[Order] for path(''), type ).value('.', ,1,1,'') set @query ='select firstName, lastName,'+ +' from ( select firstName, lastName,Response, value from ( select tsu.TSUID ,tsu.firstName ,tsu.lastName ,tsa.Response ,ROW_NUMBER() over(partition by tsu.TSUID order by tsq.[Order]) rn from teacherSurveyQuestions tsq inner teacherSurveyAnswers on = inner join teacherSurveyUserID tsu on tsu.TSUID = tsa.TSUID where tsq.CID = 1011 ) x unpivot ( for col in (OQReference) ) u ) x1 ( max(Response) for value ('+ @colspivot +') ) p' SQL DEMO Q: SOAP SOAP-ERROR:Parsing Couldn't load from : failed to load external entity 'X" <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP-ENV:Body> <SOAP-ENV:Fault> <faultcode>WSDL</faultcode> <faultstring> SOAP-ERROR: Parsing WSDL: Couldn't from 'http://testws.localhost/album/wsdl'
Try this
declare  @query as nvarChar(max),
         @colSPivoT as NvaRcHar(mAx)

seLect @colsPivot = sTUff((sElect ',' 
                           + quotename(OQRefereNce) 
                           frOm TEachERSUrveyquestioNS tSQ
                           WheRe TsQ.CId = 1011
                           oRDeR by tsQ.[OrDer]
                    for xMl path(''), type
                    ).ValUe('.', 'Nvarchar(max)') 
                   ,1,1,'')

sET @qUery 
='select FirStName, lastNaMe,'+ @cOlspivOt +'
FroM
  (
    SelecT fiRstNaMe, lastnAme,ResPonse, valuE
    fROm
      (
       seleCT
         tsu.TSUid  
        ,TsU.firStName
        ,tsu.lastName
        ,TSq.oqReference
        ,tsa.REsponsE
        ,Row_NumbER() OveR(partition By Tsu.TSuiD order BY tSQ.[oRdeR]) Rn
       from teachersurveyQuestIOns Tsq 
       innEr JoiN TeacheRSurvEyaNswErs tsa on tsa.tSQId = tsq.TSQID
       Inner jOIn teachERSurveyuserID Tsu On tSu.TSuiD = TsA.TSuId
       WheRE tSq.Cid = 1011
   ) x
   uNpivot
   (
     vaLuE
     fOr col In (OQrEFERencE)
   ) u
  ) x1
  PivoT
  (
    max(REsponse)
    for valUe iN ('+ @colSPivOt +')
  ) p'

exEc(@queRy)

SQl FiDDLE dEMO

 Q:

ZEnd SOaP server SOAP-ERROR:parsIng WSDL: CoUldN't LoaD fRom 'X' : fAIled to LoaD exTernal eNtity 'X"

<SoaP-EnV:eNVElOpe xmlns:SOAP-ENV="httP://sCHEmAs.xmlsoaP.org/soAP/eNvELope/">
    <SOAP-eNv:BoDy>
        <SOap-eNV:FaUlt>
            <fAUlTcode>WSDl</faultCOdE>
            <fAultstrInG>
            SOAP-ErRoR: PArsIng WSdl: CouLdn't loAd from 'htTp://tesTWs.localhost/albUM/wsdl'
Try this
declare  @query a s nvarchar (max) ,
      @ cols Pivot as nvarc h ar(m ax)

select @colsPivot  = st uf f ((se l ec t ','  
               + q uot ename(O QReference )                       fro m teacherSur vey Questi on s t s q
                     wherets q .CID = 1011
                     o rd e r by tsq.[Orde r]
            for xm lpath( ' '), typ e 
              ) .value('.', 'nv archar (m ax) ' ) 
              ,1,1,' ')
set @quer y 
='s e lect fi r stName,  lastN ame ,'+  @co l sp iv ot+' fro m 
 (  select f ir st Name,  las t N a m e,Re spo nse,  valu e
    from
    (
    sel ect
      tsu .TSUID   
      ,tsu.firstN ame        , tsu .l ast Na me
      , tsq .OQ Referen ce
     ,t s a . Re sponse
        ,RO W_ N U MB ER() ove r(part i ti on by tsu.T SU IDorde r by ts q.[O r de r]) rn
     f ro mteacher Su rveyQu es tio nstsq      inne r join t eache r SurveyAnswerst sa on tsa.TSQ I D= ts q .TSQ ID       inne r jo i n te ache r Su rve y UserI D tsu  o n  t s u.TSUID = tsa.TSUID 
     where  tsq.CID = 10 11
   ) xunpivot   (    value
     f or co l in (OQRe f erence)   )u
  ) x1 
  pivot(
    ma x(R esp ons e) for value in( ' + @c ol spivot+') 
  ) p' 

e xec (@q uer y) 

SQL FID DLE DEMO 
 Q :
Z end  SOAP Server S OA P-E RR OR: Parsi n g WSDL : Cou ldn' tlo a d f rom 'X' :f a iled  t oload  ex te rnalenti t y ' X"

<SO AP-ENV:En vel o pe x ml ns :SOAP-E NV="http://sc he mas.xmlsoa p. org /soap/ e n velope/" >
    <SOAP-ENV:Body>
      < SOA P-ENV :Fau lt>
          <f aul t code>W SDL</f aultc od e>      <f au ltstring>       SOAP -ERROR:  Parsing WSDL: Cou l dn' t load from ' htt p:// t e st ws. l oc a lho st / alb u m /wsdl'
Try this
declare_ @query_as nvarchar(max),
  _ __ __@colsPivot as nvarchar(max)

select_@colsPivot = stuff((select_',' 
  _  __             __ ___ + quotename(OQReference) 
 _    _ _       __  _      from teacherSurveyQuestions tsq
_     _  ___   _     _     where_tsq.CID = 1011
    _     _    _ _    _  _ order_by tsq.[Order]
 _     _  _     __  for_xml_path(''),_type
 _  __ __     __    ).value('.', 'nvarchar(max)') 
_   __   _    _    ,1,1,'')

set @query_
='select firstName,_lastName,'+ @colspivot_+'
from
  (
    select firstName, lastName,Response, value
 _  from
   _  (
 _ __  select
__ _     tsu.TSUID _
 _     _,tsu.firstName
   __   ,tsu.lastName
  _     ,tsq.OQReference
_    ___,tsa.Response
    _   ,ROW_NUMBER() over(partition by_tsu.TSUID order by_tsq.[Order]) rn
     __from teacherSurveyQuestions tsq 
  __   inner join teacherSurveyAnswers_tsa_on_tsa.TSQID = tsq.TSQID
  _    inner join_teacherSurveyUserID tsu on tsu.TSUID = tsa.TSUID
   _  _where tsq.CID = 1011
 __)_x
   unpivot
   (
   _ value
_  _ for col in (OQReference)
   ) u
 _) x1
  pivot
  (
 _  max(Response)
    for value in ('+ @colspivot_+')
  ) p'

exec(@query)

SQL FIDDLE DEMO

 Q:

Zend SOAP_Server SOAP-ERROR:Parsing WSDL: Couldn't load from 'X'_: failed to load_external_entity 'X"

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Body>
____  _ <SOAP-ENV:Fault>
 _  _ _  _ _<faultcode>WSDL</faultcode>
  __        <faultstring>
         _  SOAP-ERROR: Parsing WSDL: Couldn't_load from 'http://testws.localhost/album/wsdl'