Insert data thru a while loop and turn the data into a string??

[rogerzebra]

Hi All,
I have an issue with an insert that gets me headache at this point, I can't see what I'm missing so another pair of eyes would be much appreciated on this one. So, I'm trying to insert a policyNo, FirstName, LastName,FirstName1,LastName2 etc into temp table.

Here is my tmp table
"ID int identity(1,1),PolicyNo,FirstName,LastName,Title,
FirstName1,LastName1,Title1..etc

I'm trying to achive the following result in one column:
Policy,Firstname,LastName,Title,FirstName1,LastName1,Title1
1001 Donald,Duck,President,Kajsa,Duck,VicePresident..etc

instead I get this
Policy,Firstname,LastName,Title,FirstName1,LastName1,Title1
1001 Donald,Duck,President,null,null,null
1001 null,null,null,Kajsa,Duck,VicePresident
..etc

set nocount on 
declare @i int 
declare @iRwCnt int
declare @Policy varchar(100)
declare @sValue varchar(100)
declare @sValue1 varchar(100)
declare @sValue2 varchar(100)


set @i = 1 

create table #t1
 ( ID int identity(1,1)
		,Policynumber varchar(100)
		,FirstName varchar(100)
		,LastName varchar(100)
		,OfficerStatus varchar(100)
		,FirstName1 varchar(100)
		,LastName1 varchar(100)
		,OfficerStatus1 varchar(100)
		,FirstName2 varchar(100)
		,LastName2 varchar(100)
		,OfficerStatus2 varchar(100)
		,FirstName3 varchar(100)
		,LastName3 varchar(100)
		,OfficerStatus3 varchar(100)
		,FirstName4 varchar(100)
		,LastName4 varchar(100)
		,OfficerStatus4 varchar(100)
		)


insert into #t1(Policynumber,FirstName,LastName,OfficerStatus)
select distinct bp.Policynumber,GivenName,SurName,ltrim(ni.PositionCd)as OfficerStatus
from java.NameInfo ni											
left join java.basicpolicy bp on ni.systemid = bp.systemid
left join java.addr ad on bp.systemid = ad.systemid
left join java.namedinsured na on na.systemid = bp.systemid
where month(bp.Expirationdt) = 12 
       and year(bp.ExpirationDt) = 2008 
	   and bp.cmmcontainer = 'policy' 
       and ni.NameTypeCd = 'OfficerName'
--testPol that has 4 people and titles----
  and bp.policynumber = 'CWC00104300'

set @iRwCnt = @@ROWCOUNT 

create clustered index idx_tmp on #t1(ID) WITH FILLFACTOR = 100 

while @i <= @iRwCnt
begin 

select @Policy = policynumber,@sValue = FirstName,@sValue1 = LastName,@sValue2 = OfficerStatus --,policynumber 
from #t1 where ID = @i --(select max(ID)from #t1 where ID = @i)

set @i = @i + 1
if @i >=1
begin
update #t1 
set 
FirstName1 = FirstName
,LastName1 = LastName
,OfficerStatus1 = OfficerStatus
from #t1 
where ID = 1
end

if @i >=2
begin
update #t1 
set 
FirstName2 = FirstName
,LastName2 = LastName
,OfficerStatus2 = OfficerStatus
from #t1 
where ID = 2
end

if @i >=3
begin
update #t1 
set 
FirstName3 = FirstName
,LastName3 = LastName
,OfficerStatus3 = OfficerStatus
from #t1 
where ID = 3
end

if @i >=4
begin
update #t1 
set 
FirstName4 = FirstName
,LastName4 = LastName
,OfficerStatus4 = OfficerStatus
from #t1 
where ID = 4
end

end
select * from #t1

drop table #t1

As always I'm very thankful to have this community to share ideas with. Thx in advance

 

[RiverGuy]

I'm not really sure exactly what you're trying to do, but at first glance it looks like you're going through a loop and updating the same set of records for each iteration of the loop.

So if you have more than four records, you're only updating IDs one through four.

 

[rogerzebra]

Thanks RiverGuy for your respond. Yes, I realized that it did only loop thru the first id's, so you are right. I added the ID int identity(1,1) part to use for looping thru the names within a policy and not to set an id for each policy as in the posted query. I have functional query where I'm using a cursor, but I wanted to solve the issue without a cursor. I guess I have to go back to the drawing board again. Thanks for your input.