How do I merge data into one record
-
I've been using SQL for several production applications, but I have to say, my knowledge level of SQL is still at the beginner level, maybe just scraping the intermediate knowledge level. I need some help from those of you who are more experienced with SQL code. I want to take a table with several records that have identical file numbers and merge one of the fields in the table with the other records. The consolidated record can be written into another table. Not all the records have duplicates, and I want to just write those records to the second table also. For example the table I would be reading from would contain the following: FileNo FlagInfo 1234 A 1234 B 1234 C 2345 A 3456 A 3456 B The table being written to would contain the following: FileNo FlagInfo 1234 A,B,C 2345 C 3456 A,B Can this be done with a stored procedure or should I just write VB code to read the file in and write out the single records? Any suggestions? :confused:
Lost in the vast sea of .NET
-
I've been using SQL for several production applications, but I have to say, my knowledge level of SQL is still at the beginner level, maybe just scraping the intermediate knowledge level. I need some help from those of you who are more experienced with SQL code. I want to take a table with several records that have identical file numbers and merge one of the fields in the table with the other records. The consolidated record can be written into another table. Not all the records have duplicates, and I want to just write those records to the second table also. For example the table I would be reading from would contain the following: FileNo FlagInfo 1234 A 1234 B 1234 C 2345 A 3456 A 3456 B The table being written to would contain the following: FileNo FlagInfo 1234 A,B,C 2345 C 3456 A,B Can this be done with a stored procedure or should I just write VB code to read the file in and write out the single records? Any suggestions? :confused:
Lost in the vast sea of .NET
I can think of two possibilities: 1) PIVOT if you are using SQL Server 2005 2) Use a user defined function like this:
ALTER FUNCTION dbo.NormalizeChildren ( @FileNo INT ) returns VARCHAR(8000) AS BEGIN DECLARE @result VARCHAR(8000) SELECT @result = ISNULL(@result, '') + CASE WHEN @result IS NULL THEN '' ELSE ',' END + FlagNo FROM FlagTable WHERE FileNo = @FileNo RETURN @result END GO --Call the function like this SELECT FileNo, dbo.NormalizeChildren(FileNo) AS Children FROM FileTableMark's blog: developMENTALmadness.blogspot.com