Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: Multiple rows to one comma-separated value

    Hey all I have a query like this:
    Code:
    SELECT  
        *
    FROM 
        [Tools_Login].[dbo].[tblUsers]  AS Users 
    INNER JOIN [Tools_Login].[dbo].[tblTrackingNumbers] AS Tracking 
        ON Tracking.userID = Users.userID
    INNER JOIN [Tools_Login].[dbo].[tblUserRoles] AS URoles 
        ON URoles.userID = Users.userID
    INNER JOIN [Tools_Login].[dbo].[tblRoles] AS Roles 
        ON URoles.roleID = Roles.roleID
    INNER JOIN [LPersistent].[dbo].[tblSchoolUsers] AS SUsers 
        ON Users.userID = sUsers.userID
    INNER JOIN [LPersistent].[dbo].[tblSchools] AS S 
        ON SUsers.sID = S.sID
    WHERE 
        Users.orgID = 76
    AND 
        Users.userID = 58104
    This outputs the following:
    Code:
    userID | trackingNumberID | roleID | sID | Lots more data --->
    -------+------------------+--------+-----+-------------------- 
    58104  | 569201444        | 54     | 274 | Lots more data --->
    58104  | 569201444        | 81     | 274 | Lots more data --->
    58104  | 569201444        | 56     | 274 | Lots more data --->
    As you can see everything is a duplicate but roleID. If I take out the AS URoles and AS Roles from the query and run it again, it brings only 1 record back. But the issue is that I NEED to have both the **Roles and URoles in the query to get information from for that user.

    In all, this is what I need to query to look like:
    Code:
    userID | trackingNumberID | roleID   | sID | Lots more data --->
    -------+------------------+----------+-----+-------------------- 
    58104  | 569201444        | 54,81,56 | 274 | Lots more data --->
    Any SQL guru that can help me with this would be great!

  2. #2
    Join Date
    Sep 2016
    Posts
    1
    This -


    Select top 10 fullsc.Gender,
    Left(fullsc.IDS,Len(fullsc.IDS)-1) As IDS
    From
    (
    Select distinct hr2.Gender,
    (
    Select hr1.NationalIDNumber + ' , ' AS [text()]
    From [HumanResources].[Employee] hr1
    Where hr1.Gender = hr2.Gender
    ORDER BY hr1.Gender
    For XML PATH ('')
    ) IDS
    From [HumanResources].[Employee] hr2
    ) fullsc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •