How to retrieve multiple records of a particular id in one row using SQL Server

Question

I have 3 tables as follows

Issues

issue_num     issue_desc       issue_status   issue_date
-----------------------------------------------------------------------
abc123        login issue           1           2017-03-15 00:00:00.000
abc345        session issue         1           2017-03-15 00:00:00.000
abc334        logeger issue         1           2017-03-15 00:00:00.000

Users

id      user_id     user_first_name     user_last_name      user_email_id
--------------------------------------------------------------------------    
1       yash123         yashn               ray             yash@gmail.com  
2       ray234          raah                kumar           raah@yahoo.com
3       aniket          aniket              pal             aniket@yahoo.com

Issue Assigned

issue_num      assigned_to_user_id      comment                 assign_date
------------------------------------------------------------------------------------
abc123              1                   replicating issue       2017-03-15 00:00:00.000
abc123              2                   replicating issue       2017-03-15 00:00:00.000
abc345              2                   replicating issue       2017-03-15 00:00:00.000
abc345              3                   replicating issue       2017-03-15 00:00:00.000
abc334              3                   replicating issue       2017-03-15 00:00:00.000

If you check in Issue_Assigned table, some issues are assigned to 2 users. I want to write a query where in my expected result should be following

Issue_number     issue_desc        assigned_to_user_id              comment          assign_date
---------------------------------------------------------------------------------------------------------    
abc123          login issue         yash123,ray234              replicating issue   2017-03-15 00:00:00.000
abc345          session issue       ray234,aniket               replicating issue   2017-03-15 00:00:00.000
abc334          session issue       aniket                      replicating issue   2017-03-15 00:00:00.000

What have you go so far? Hint: Look at http://stackoverflow.com/q/9726660/1662973 for some answers to create the actual CSV list. — Anthony Horne, Mar 22 '17 at 12:21
Is this assumed that if more than one users are assigned an issue, they will have same assignment comment and assign date? — DhruvJoshi, Mar 22 '17 at 12:21
You can check accepted answer [here.](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Avi, Mar 22 '17 at 12:27

SqlZim · Accepted Answer · 2017-03-22T13:21:52.643

using the stuff() with select ... for xml path ('') method of string concatenation.

select 
    issue_num
  , issue_desc
  , assigned_to_user_id = stuff((
    select distinct ','+u.user_id
    from Users u 
      inner join IssueAssigned sia
        on u.id = sia.assigned_to_user_id
    where sia.issue_num = i.issue_num
    for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
  , x.comment 
  , x.assign_date
from Issues i
  cross apply (
    select top 1 ia.comment, ia.assign_date
    from IssueAssigned ia
    where ia.issue_num = i.issue_num
    ) as x

rextester demo: http://rextester.com/QFVR26057

returns:

+-----------+---------------+---------------------+-------------------+---------------------+
| issue_num |  issue_desc   | assigned_to_user_id |      comment      |     assign_date     |
+-----------+---------------+---------------------+-------------------+---------------------+
| abc123    | login issue   | ray234,yash123      | replicating issue | 2017-03-15 00:00:00 |
| abc345    | session issue | aniket,ray234       | replicating issue | 2017-03-15 00:00:00 |
| abc334    | logeger issue | aniket              | replicating issue | 2017-03-15 00:00:00 |
+-----------+---------------+---------------------+-------------------+---------------------+

Credit to Gouri Shankar Aechoor for composing the sample data.

getting syntax exception for '=' in line 4 , for in line 10 and as — Suyash, Mar 22 '17 at 13:06
i am trying to run same in ms sql management studio giving me errors — Suyash, Mar 22 '17 at 13:35

Vecchiasignora · Answer 2 · 2017-03-22T13:36:25.437

1

Try it, it should be ok

select i.issue_num, i.issue_desc, iss.* from Issues i join 
(select distinct a.issue_num, a.comment, a.assign_date, STUFF((
                Select u.user_id + ',' AS assigner_to_user_id
                From Issue_Assigned isu join Users u on isu.assigned_to_user_id = u.id
                Where isu.issue_num = a.issue_num
                ORDER BY u.id
                For XML PATH (''), type).value('.','nvarchar(max)'
            )  ,1,1, '') as assigned_to_user_id
            from Issue_Assigned a ) iss on i.issue_num = iss.issue_num

edited Mar 22 '17 at 13:36

answered Mar 22 '17 at 12:42

Vecchiasignora

1,275
7
6

1

i will fix it, one minute – Vecchiasignora Mar 22 '17 at 13:12
getting syntex exception for ) from ISSUE_ASSIGNED a ) expected as id or quoted id – Suyash Mar 22 '17 at 13:15
I have fixed it, only change your column and tables names, i have changed my comment already, please look it above – Vecchiasignora Mar 22 '17 at 13:35
for the matching case it is showing 2 rows and also it is removing first alphabet of userId – Suyash Mar 22 '17 at 14:03

score 1 · Answer 3 · answered Mar 22 '17 at 12:43

Hope this helps

;WITH cte_Issues(issue_num,issue_desc,issue_status,issue_date) AS
(
SELECT 'abc123','login issue','1','2017-03-15 00:00:00.000' UNION ALL
SELECT 'abc345','session issue','1','2017-03-15 00:00:00.000' UNION ALL
SELECT 'abc334','logeger issue','1','2017-03-15 00:00:00.000'
)
,cte_users(id,user_id,user_first_name,user_last_name,user_email_id) AS
(
SELECT 1,'yash123','yashn','ray','yash@gmail.com' UNION ALL
SELECT 2,'ray234','raah','kumar','raah@yahoo.com' UNION ALL
SELECT 3,'aniket','aniket','pal','aniket@yahoo.com'
)
,cte_IssueAssigned (issue_num,assigned_to_user_id,comment,assign_date) AS
(
SELECT 'abc123',1,'replicating issue',CAST('2017-03-15 00:00:00.000' AS DATE) UNION ALL
SELECT 'abc123',2,'replicating issue',CAST('2017-03-15 00:00:00.000' AS DATE) UNION ALL
SELECT 'abc345',2,'replicating issue',CAST('2017-03-15 00:00:00.000' AS DATE) UNION ALL
SELECT 'abc345',3,'replicating issue',CAST('2017-03-15 00:00:00.000' AS DATE) UNION ALL
SELECT 'abc334',3,'replicating issue',CAST('2017-03-15 00:00:00.000' AS DATE)
)
,
cte_Staging AS (
        SELECT ci.issue_num,
            issue_desc,
            user_id AS assigned_to_user_id,
            comment,
            issue_date
        FROM cte_Issues ci
        INNER JOIN cte_IssueAssigned cia
            ON ci.issue_num = cia.issue_num
        INNER JOIN cte_users cu
            ON cu.id = cia.assigned_to_user_id
        )

SELECT issue_num,
    issue_desc,
    STUFF((
            SELECT ',' + b.assigned_to_user_id AS [text()]
            FROM cte_Staging b
            WHERE b.issue_num = a.issue_num
            FOR XML PATH('')
            ), 1, 1, '') AS assigned_to_user_id,
    comment,
    issue_date
FROM cte_Staging a

score 0 · Answer 4 · answered Mar 22 '17 at 12:32

0

This should give the desired result.

select T2.issue_num, issue_desc, stuff((select ',' + cast(user_id  as varchar(20))
                          from Users T1
                          where T1.id=T2.assigned_to_user_id
                          for xml path('')),1,1,'') userid,T2.comment,T2.assign_date
from IssueAssigned T2 inner join issues T3 on T2.issue_num=T3.issue_num

answered Mar 22 '17 at 12:32

Rajesh Bhat

791
3
8
20

this result i am getting in 2 seperate rows – Suyash Mar 22 '17 at 13:02
@Suyash, that is what you need right? You mentioned 3 rows in your desired output. – Rajesh Bhat Mar 23 '17 at 04:18

How to retrieve multiple records of a particular id in one row using SQL Server

4 Answers4