How to get single record results 1

SELECT c.ID,c.name,
       a1.fieldvalue AS 'Exp Date'  ,
       a2.fieldvalue AS 'Priority'  ,
       a3.fieldvalue AS 'Whatever1' ,
       a4.fieldvalue AS 'Whatever2' ,
       a5.fieldvalue AS 'Whatever3'
FROM Contacts c 
     outer join Addfields a1 ON a1.ID = c.ID and a1.fieldname = 'Exp Date' 
     outer join Addfields a2 ON a2.ID = c.ID and a2.fieldname = 'Priority' 
     outer join Addfields a3 ON a3.ID = c.ID and a2.fieldname = 'Whatever1' 
     outer join Addfields a4 ON a4.ID = c.ID and a3.fieldname = 'Whatever2'
     outer join Addfields a5 ON a5.ID = c.ID and a3.fieldname = 'Whatever3'

select   c.id, 
         c.name, 
         case when b.fieldname = 'Exp Date' then b.fieldvalue else NULL end 'Exp Date', 
         case when b.fieldname = 'Priority' then b.fieldvalue else NULL end 'Priority' 
from     contacts c inner join AddFields 
on       (a.id = b.id)

select   c.id, 
         c.name, 
         max(case when b.fieldname = 'Exp Date' then b.fieldvalue else '01/01/1900' end) 'Exp Date', 
         max(case when b.fieldname = 'Priority' then b.fieldvalue else 'L' end) 'Priority' 
from     contacts c inner join AddFields 
on       (a.id = b.id)
group by c.id, c.name

DECLARE @rc int, @err int

-- CREATE A TEMPORARY TABLE WITH ALL PROJECTS AND STATE NAMES + SEQUENTIAL #:
SELECT RecID = identity(int,1,1),
       p.ProjectID,
       prst.StateName
INTO #all
FROM Projects p 
             left join Project_State prst
                         ON p.ProjectID = prst.ProjectID
             left join Policy_state post
                         ON prst.StateID = prst.StateID
order by p.ProjectID, prst.StateName

-- CREATE ANOTHER TEMP TABLE WITH MINIMUM RecID for a project:
SELECT ProjectID, min(RecID) as MinRecID
INTO #min 
FROM #all
group by ProjectID

SELECT a.ProjectID, a.RecID,
       a.RecID - m.MinRecID + 1 as SubRecID,  -- A sub SeqID for each project
       a.StateName
into #last
from #all a inner join #min m on a.ProjectID = m.ProjectID
order by a.RecID

-- OPTIONAL, BUT CLEARS SPACE IN TEMPDB:
drop table #all
drop table #min

/* NOW WE HAVE IN #last:
RecID   projectID   SubRecID       State
-------------------------------------------
1          01          1           Alabama
2          01          2           Arizona
3          01          3           Carolina
4          02          1           Alabama
5          02          2           Mississipi
6          03          1           Dallas
...
*/

create table #States4project
(ProjectID char(2), 
 State1   varchar(100),
 State2   varchar(100),
 State3   varchar(100),
......
 State50   varchar(100)
)

-- INSERT ProjectID:
insert into #States4Project (ProjectID)
select ProjectID from Projects

-- AND NOW UPDATE ALL STATES IN LOOP:
set @rc = 1
while @rc > 0 and @err = 0
begin
      EXEC ('UPDATE #States4project' +
            ' SET State' + convert(varchar(2),@rc) + ' =' +
            ' l.StateName' +
            ' FROM  #States4project s, #last l' +
            ' WHERE  s.ProjectID = l.ProjectID' +
            '   AND  l.SubRecID = ' + convert(varchar(2),@rc) )

     set @rc = @@rowcount, @err = @@error
  
end

if @err = 0
     select * from #States4project
GO