January 16, 2013 at 3:03 am
Hi to all,
Please bear with me. anyways, this is the tables i have
Table1
Name Company OtherVisaID
Mike ABC 1
Joey CBA 2
Table2
OtherVisaID TypeID Remarks
1 1 test1
1 2 test2
2 3 test3
2 2 test4
2 1 test5
Table3
TypeID Description
1 US
2 UK
3 SG
Result should be like this..
OtherVisaID Name Company List of Visa
1 Mike ABC US;UK
2 Joey CBA US;UK;SG
so as you can see it was separated by ";" hope you understand my question..
thanks in advance.
January 16, 2013 at 3:33 am
Hi,
Try:
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
Hope this help.
January 18, 2013 at 7:49 am
If you can use SQLCLR you could use GROUP_CONCAT for SQL Server[/url] and write your SQL like this:
SELECT t1.OtherVisaID,
t1.Name,
t1.Company,
dbo.GROUP_CONCAT_D(t3.DESCRIPTION, ';') AS ListOfVisa
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.OtherVisaID = t2.OtherVisaID
JOIN Table3 AS t3 ON t3.OtherVisaID = t2.OtherVisaID
GROUP BY t1.OtherVisaID,
t1.Name,
t1.Company;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply