You can use function to fetch data from multiple records into one field. If for example we have table users and hobbies.
user_id | user_name | address |
1 | John Walker | Bangladesh |
2 | Frank McLane | Nepal |
3 | Leon Kennedy | USA |
user_id | hobby |
1 | Reading |
1 | Sleeping |
1 | Walking |
2 | Swimming |
3 | Help People |
If we're asked to display the user's name along with his/her hobbies, first we create a function to fetch the hobbies of a user into a single column. The function is as follows
CREATE FUNCTION [dbo].[get_hobbies]
(
@usr_id int
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @hobbies varchar(255)
DECLARE @temp varchar(255)
SET @hobbies = ''
SET @temp = ''
DECLARE HbCur CURSOR FOR SELECT hobby FROM users inner join hobbies on users.[user_id] = hobbies.[user_id] WHERE users.[user_id]=@usr_id
OPEN HbCur
FETCH next FROM HbCur INTO @temp
WHILE @@Fetch_Status = 0
BEGIN
SET @hobbies = @hobbies + @temp + ', '
FETCH next FROM HbCur INTO @temp
END
CLOSE HbCur
DEALLOCATE HbCur
--remove the last unnecessary comma
SET @hobbies = substring(@hobbies, 1, len(@hobbies)-1)
RETURN @hobbies
END
The function is quite self-explanatory. The body of the function starts after the keyword BEGIN and end at the END keyword. It takes the user_id as a parameter which will be used to select all hobbies of that user. Here, two variables are declared, @hobbies and @temp. These two variables are used to store the result of the hobbies queried. Next, we declare a CURSOR that will hold the result hobbies from the query, and then can be iterated to get each hobby. Next, there is a loop which will fetch every single hobby to the @temp variable which will then be appended to @hobbies variable that will hold all of the hobbies separated by comma. Last, the function returns all of the hobbies.
To test the function you can run the following sql
SELECT [dbo].[get_hobbies] (1)
This is the output when I run the query.