SQL query with comma separated values

Question

I have a requirement wherein I have 3 tables

ContactInfo

idContactinfo  FirstName  Lastname      idCOmpanyid
---------------------------------------------------
1               Blah1      Blah1            1
2               Blah2      Blah2            1
3               Blah3      Blah3            1
4               Blah4      Blah4            1

ContactRole

idcontactrole     IdContactRoleName      idContactInfo   
------------------------------------------------------
1                       1                     1
2                       1                     2
3                       1                     3
4                       2                     4

IdContactRoleLookup

idcontactRole           RoleName
----------------------------------
1                        Admin
2                        Secretary

I need a query that gives me a result like this

idcontactrolename            Contacts
--------------------------------------------------------
Admin                    Blah1,Blah1;Blah2,Blah2,Blah3,Blah3
Secretary                Blah4,Blah4

This is what I have till now !!

SELECT l.sValue
    , SUBSTRING((
                        SELECT C.sLast + ',' + C.sFirst + ';'
                        FROM contactinfo c
                        inner join contactrole crole
                        ON crole.idcontactinfo = c.idcontactinfo
                        WHERE crole.idcompany = '<<blah>>'
                        and c.idcompany = '<<blah>>'
                        FOR XML PATH('')

                    ), 1, 1000000)
FROM CONTACTROLE CR
inner join contactinfo c on 
cr.idcontactinfo = c.idcontactinfo 
inner join lookupdata l on cr.idlookuprole = l.idlookupdata
where c.idcompany = '<<blah>>'

Your query and your table definitions do not match? Which are correct? — sgeddes
– sgeddes, Commented Feb 26, 2013 at 16:03

John Woo · Accepted Answer · 2013-02-26 16:05:13Z

2

SELECT
     RoleName,
     STUFF(
         (
            SELECT  ';' + b.FirstName + ', ' + b.LastName
            FROM    ContactRole a
            INNER JOIN ContactInfo b
                  ON a.idContactInfo = b.idContactinfo
            WHERE   a.IdContactRoleName = r.idcontactRole
            FOR XML PATH (''))
            , 1, 1, '')  AS NamesList
FROM  IdContactRoleLookup r
GROUP BY idcontactRole, RoleName

SQLFiddle Demo

answered Feb 26, 2013 at 16:05

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JudgeProphet · Accepted Answer · 2013-02-26 16:03:27Z

1

I Would do somethng like this, where you use the ID of the main QUERY into your SUBQUERY

SELECT     
l.sValue
    , SUBSTRING((
                        SELECT C.sLast + ',' + C.sFirst + ';'
                        FROM contactinfo c
                        inner join contactrole crole
                        ON crole.idcontactinfo = c.idcontactinfo
                        WHERE c.idcompany = '<<blah>>'
AND c.idcontactrole = CR.idcontactrole
                        FOR XML PATH('')

                    ), 1, 1000000)

FROM CONTACTROLE CR
inner join lookupdata l on cr.idlookuprole = l.idlookupdata

This will give you all the role for one Compagnie. If the role does'nt exist you will have a NULL value.

answered Feb 26, 2013 at 16:03

JudgeProphet

1,7493 gold badges27 silver badges44 bronze badges

Comments

Taryn · Accepted Answer · 2013-02-26 16:09:37Z

1

You can use CROSS APPLY to get the result:

select distinct l.RoleName,
  left(list, len(list) -1) list
FROM CONTACTROLE CR
inner join IdContactRoleLookup l 
  on cr.IdContactRoleName = l.idcontactRole
cross apply
(
  select C1.Lastname + ',' + C1.FirstName + ';'
  from CONTACTROLE CR1
  inner join contactinfo c1
    on cr1.idContactInfo = c1.idContactinfo
  where cr.IdContactRoleName = cr1.IdContactRoleName
  FOR XML PATH('')
) t2 (list)

See SQL Fiddle with Demo

answered Feb 26, 2013 at 16:09

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

SQL query with comma separated values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related