InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:02:04 GMT20http://www.sqlservercentral.com/Forums/Topic1407712-392-1.aspxIf you can use SQLCLR you could use [u][url=http://groupconcat.codeplex.com/]GROUP_CONCAT for SQL Server[/url][/u] and write your SQL like this:[code="sql"]SELECT t1.OtherVisaID, t1.Name, t1.Company, dbo.GROUP_CONCAT_D(t3.DESCRIPTION, ';') AS ListOfVisaFROM Table1 AS t1 JOIN Table2 AS t2 ON t1.OtherVisaID = t2.OtherVisaID JOIN Table3 AS t3 ON t3.OtherVisaID = t2.OtherVisaIDGROUP BY t1.OtherVisaID, t1.Name, t1.Company;[/code]Fri, 18 Jan 2013 07:49:33 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1407712-392-1.aspxHi,Try:[code="sql"]SELECT t1.OtherVisaID, t1.Name, t1.Company, STUFF( (SELECT ';' + t3.Description FROM Table2 as t2 JOIN Table3 as t3 ON t3.OtherVisaID = t2.OtherVisaID WHERE t2.OtherVisaID = t1.OtherVisaID FOR XML PATH(''), TYPE).value('.', 'varchar(max)') ,1, 1, '') as ListOfVisa,FROM Table1 as t1[/code]Hope this help.Wed, 16 Jan 2013 03:33:47 GMTimexhttp://www.sqlservercentral.com/Forums/Topic1407712-392-1.aspxHi to all,Please bear with me. anyways, this is the tables i haveTable1Name Company OtherVisaIDMike ABC 1Joey CBA 2Table2OtherVisaID TypeID Remarks1 1 test11 2 test22 3 test32 2 test42 1 test5Table3TypeID Description1 US2 UK3 SGResult should be like this..OtherVisaID Name Company List of Visa 1 Mike ABC US;UK 2 Joey CBA US;UK;SGso as you can see it was separated by ";" hope you understand my question..thanks in advance.Wed, 16 Jan 2013 03:03:18 GMTernesto.yandoc