Geeks With Blogs
Sathya Narayanan Srinivasan .NET Developer

This was the question from Pinal Dave's blog and i have answered the same. Thought this might be useful for my friends too and posting the same here.

Hi,

I’ve following problem, which I’ve not been able to do successfully. Your help will be appreciated.

Table has following 2 columns

DocNum DocEntry
1 234
2 324
2 746
3 876
3 764
4 100
4 387

Expected result is as follow

1 234
2 324, 746
3 876, 764
4 100, 387

Thanks
Rahul Jain

  • Your comment is awaiting moderation.

    Hi Rahul Jain,

    Here is the answer :

    select distinct
    x.id
    ,substring(
    (select textfield + ‘,’ from testtable y where x.id=y.id order by x.Id for xml path(”))
    ,0
    ,LEN((select textfield + ‘,’ from testtable y where x.id=y.id order by x.Id for xml path(”))))
    from testtable x

    Here is the output :

    1 234
    2 324,746
    3 876,764
    4 100,387

    Thanks and Regards
    Sathya

Posted on Monday, August 16, 2010 12:28 PM | Back to top


Comments on this post: Concatenate row data from SQL using For XML instead of coalesce

# re: Concatenate row data from SQL using For XML instead of coalesce
Requesting Gravatar...
FOR XML causes issues in case your string contains characters such as &,>,< etc as these will get replace with &amp,....
Left by Chintan Gandhi on Feb 02, 2012 2:46 PM

Your comment:
 (will show your gravatar)


Copyright © sathya | Powered by: GeeksWithBlogs.net