Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to concatenate row columns and joined in another table Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 6:01 PM
Points: 2, Visits: 1
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.
Post #1407712
Posted Wednesday, January 16, 2013 3:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 6:54 AM
Points: 40, Visits: 168
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.


http://www.imoveisemexposicao.com.br
Post #1407725
Posted Friday, January 18, 2013 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
If you can use SQLCLR you could use GROUP_CONCAT for SQL Server 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
Post #1408924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse