• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

tsql looping

hi there got a problem im doing  a data migratio and want to concatnate muiltiple fields into a single field
the problem is this

the table structure is like this

 id(int), applicantid (int), field1(varchar) ,field2 (varchar), concatenate(varchar)

problem is there are multiple rows of data for each applicant

so i need to loop through for  each applicantid , concatenate the fields into a single field and delete the duplicates, im primarily a software dev and tsql isnt my strongest fields, it would be easy to do if there werent multiple rows for each applicant. any help would be appreciated
0
klace06
Asked:
klace06
  • 10
  • 5
  • 2
1 Solution
 
tigin44Commented:
cursor for the aplicant id
open
loop
     cursor for the applicants records based on the applicantid
     open
     loop
             do your job in here          
     end loop
end loop


you can achive your goal by using cursor in a structure like the one above.
 Regards
0
 
klace06Author Commented:
here is what i haev so far, can you tel me if this is correct and if so how to now store the value of the concatenated field and delete duplictates

cheers mate
SET NOCOUNT ON
 
  
 
DECLARE @id int,@appID int,  @Edu nvarchar
 
  
DECLARE applications_cursor CURSOR FOR
 
SELECT intApplicantEducationID FROM dtlApplicantEducation 
 
ORDER BY intApplicantEducationID
 
 
OPEN applications_cursor
 
  
FETCH NEXT FROM applications_cursor
 
INTO @id
 
  
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 -- Declare an inner cursor based  
 
    -- on au_id from the outer cursor.
 
    DECLARE applicants_cursor CURSOR FOR
 
  SELECT intApplicantID FROM dtlApplicantEducation 
where intApplicantEducationID =@id
 
    OPEN applicants_cursor
 
    FETCH NEXT FROM applicants_cursor INTO @appID
 
  
    IF @@FETCH_STATUS <> 0
 
      print @appID
 
  
 
    WHILE @@FETCH_STATUS = 0
 
    BEGIN
 
        
 
        SELECT @edu= @edu+ ''+ vchSchoolCollegeUni +  '<br />' +  vchQualifications + '<br />' +  
	CONVERT(varchar, dteFrom)  + '<br />' +  
	CONVERT(varchar, dteTo)   
	FROM dtlApplicantEducation 
	where intApplicantID=@appID
 
        PRINT @edu
 
        FETCH NEXT FROM applicants_cursor INTO @appID
 
    
 
    END
 
  
 
    CLOSE applicants_cursor
 
    DEALLOCATE applicants_cursor
 
 
    -- Get the next author.
 
 FETCH NEXT FROM applications_cursor INTO @id
 
 
    
 
END
 
  
 
CLOSE applications_cursor
 
DEALLOCATE applications_cursor
 
GO

Open in new window

0
 
klace06Author Commented:
the query above seems to loop forever am I doing something wrong?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tigin44Commented:
check this out

SET NOCOUNT ON
 
 
 
DECLARE @id int,@appID int,  @Edu nvarchar
 
 
DECLARE applications_cursor CURSOR FOR
 
SELECT DISTINCT intApplicantEducationID FROM dtlApplicantEducation
 
ORDER BY intApplicantEducationID
 
 
OPEN applications_cursor
 
 
FETCH NEXT FROM applications_cursor
 
INTO @id
 
 
 
WHILE( @@FETCH_STATUS <> 0)
 
BEGIN
 -- Declare an inner cursor based  
 
    -- on au_id from the outer cursor.
 
    DECLARE CrOWS CURSOR FOR
 
  SELECT Edu  FROM dtlApplicantEducation
      where intApplicantEducationID =@id
 
    OPEN CrOWS
 
    FETCH NEXT FROM CrOWS INTO @Edu
 
 
    WHILE (@@FETCH_STATUS <> 0)
 
    BEGIN
 
       
 
        SELECT @edu= @edu+ ''+ vchSchoolCollegeUni +  '<br />' +  vchQualifications + '<br />' +  
      CONVERT(varchar, dteFrom)  + '<br />' +  
      CONVERT(varchar, dteTo)  
      FROM dtlApplicantEducation
      where intApplicantID = @id
 
        PRINT @edu
 
        FETCH NEXT FROM CrOWS INTO @Edu
 
   
 
    END
 
 
 
    CLOSE CrOWS
 
    DEALLOCATE CrOWS
 
 
    -- Get the next author.
 
 FETCH NEXT FROM applications_cursor INTO @id
 
 
   
 
END
 
 
 
CLOSE applications_cursor
 
DEALLOCATE applications_cursor
 
GO
0
 
Snarf0001Commented:
Might be easier to just create a temporary function to do the joining, and then drop it when you're done.

Given your sample table:
create function dbo.tmpJoin(@applicantid int)
	returns varchar(1000)
as
begin
 
	declare @r varchar(1000)
	set @r = ''
 
	select @r = @r + field + ', '
	from (
		select applicantid, field1 as field from conc where applicantid = @applicantid
		union
		select applicantid, field2 as field from conc where applicantid = @applicantid
	) x order by field
 
	return @r
 
end
 
select applicantid, dbo.tmpJoin(applicantid)
from (select distinct applicantid from conc) x

Open in new window

0
 
klace06Author Commented:
does not work mate the second cursor are you opening this based on the @appID as you suggested? you dont seem to be im confused
0
 
klace06Author Commented:
`sorry Snarf0001:  i dont understand your login can you use my sample query and show me how that culd possibly work?
0
 
Snarf0001Commented:
Sure, but i think i misunderstood, and made that far more complex than it needed to be.
Do you mean you have 5 fields, Edu, vchSchoolCollegeUni, vchQualifications, dteFrom and dteTo, and you need to combine those for each row first, and then trim out duplicated rows?

If so you can accomplish with one simple query:

select distinct intApplicantID,
      Edu + + ''+ vchSchoolCollegeUni +  '<br />' +  vchQualifications + '<br />' +  
      CONVERT(varchar, dteFrom)  + '<br />' +  
      CONVERT(varchar, dteTo)

or, do you need to remove duplicates from the individual fiels FIRST?  ie, so vchSChoolCollegeUni only ever has one instance of each value?
0
 
klace06Author Commented:
the main problem is that the each applicant has mutiple rows so you will see many rows with the same applicantID , now i want to concatenate  filelds for each row into one column.


so ie an applcant has a row for university education, and another row for college education and another row for secondary school

i want a single row with the all education details  ie (university education, + "" +college education+  secondary school ) concatenated into a single field call Education
i hope this makes it clearer i think your orginal query was on the righht path , i just dont understand it i would guess that i need a correleated query for this?
0
 
klace06Author Commented:
then finally i only want a single row for each applicant as there is not need for mutile rows as education details have been put into a single field
0
 
Snarf0001Commented:
Think i've got it now, just to be sure, with the other fields, you want a single row (for each applicantid) with something like:

univeristy + uni.qulifications + univ.datefrom + univ.dateto    +    college + coll.qual + colldatefrom..... and so on?
0
 
klace06Author Commented:
yes exactly
0
 
Snarf0001Commented:
try this.

Depending on how much data you have, may need to bump up the varchar(1000).
Create the function first, then run the select.
create function dbo.tmpJoinFields(@applicantid int)
	returns varchar(1000)
as
begin
 
	declare @r varchar(1000)
	set @r = ''
 
	--this will join the rows in the result set that have already 
	--had the columns joined in the subquery below
	select @r = @r + JoinedFields + '<br /><br />'
	from (
		--join the columns on the individual rows first
		--and filter out dups with distinct
		SELECT distinct Edu + '' + vchSchoolCollegeUni +  '<br />' +  vchQualifications + '<br />' +  
			CONVERT(varchar, dteFrom)  + '<br />' +  
			CONVERT(varchar, dteTo) as JoinedFields
		FROM dtlApplicantEducation 
		where intApplicantID = @id
	) x
 
	return @r
 
end
 
 
--this will now subselect all distinct intApplicantIDs, and then run the function once for each one
select intApplicantID, dbo.tmpJoinFields(intApplicantID)
from (select distinct intApplicantID from dtlApplicantEducation) x

Open in new window

0
 
klace06Author Commented:
Msg 137, Level 15, State 2, Procedure tmpJoinFields, Line 19
Must declare the variable '@id'.
Msg 156, Level 15, State 1, Procedure tmpJoinFields, Line 28
Incorrect syntax near the keyword 'select'.
0
 
Snarf0001Commented:
sorry, close to the bottom of the function where it says:

  where intApplicantID = @id

change to

  where intApplicantID = @applicantid


and then don't run the full thing at once, create just the function first, without the select statement at the bottom, then once the function's created, THEN run the select statement by itself.
0
 
klace06Author Commented:
i guess @if d would be  @applicantid  but  i still get

Msg 156, Level 15, State 1, Procedure tmpJoinFields, Line 28
Incorrect syntax near the keyword 'select'.
0
 
klace06Author Commented:
wicked that works you'r a star mate ill study this code till i understand it fully.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now