This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output and concat the string data without using STUFF function.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 |
id strname ----------- ------------ 1 a 1 b 1 c 2 x 2 y 3 p 3 q 3 r |
Expected Output:
|
1 2 3 4 5 |
id ConcatString ----------- --------------- 1 a, b, c 2 x, y 3 p, q, r |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_strings (id int, strname varchar(50)) GO INSERT INTO tbl_strings VALUES (1,'a'),(1,'b'),(1,'c') ,(2,'x'),(2,'y') ,(3,'p'),(3,'q'),(3,'r') GO |
Solution by creating a function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE FUNCTION [dbo].[GetConcatString] ( @id int ) RETURNS varchar(max) AS BEGIN DECLARE @str VARCHAR(max) SELECT @str = COALESCE(@str + ', ', '') + strname FROM tbl_strings WHERE id = @id RETURN @str END GO SELECT id, dbo.GetConcatString(id) as ConcatString FROM tbl_strings GROUP BY id GO |
Please try the different solution for this puzzle and share it via comment...
