Concatenating value from couple of rows into one column (group by)

Here is the query I have for the tables I need.

--I would not use the where issue_ID in as I need it for all issues. Below is just a subset of the results.

SELECT [Name] as [Issue_Type]
      ,Issue_Id
  FROM [IMS].[dbo].[CategoryIssues] a
  left join [IMS].[dbo].[Categories] b
  on a.Category_Id = b.Id
where Issue_Id in (10004516,
10004608,
10005483)

Result:
Issue_Type      Issue_Id
Credit Bureau      10004516
Collections      10004516
Dealer      10004608
Collections      10004608
Credit Bureau      10005483
Collections      10005483

I need it to say

Issue ID            Issue Type
10004516         Credit Bureau; Collections
10004608         Dealer; Collections
flagshipcreditAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Could you please post some dummy data for testing putpose (for both CategoryIssues and Categories)?
flagshipcreditAuthor Commented:
Categories Issues
Id      Name
1      SCRA
2      Credit Bureau
3      Fraud
4      Identity Theft
5      Attorney contact (including letter)
6      Payment/Billing

Category Issues
Category_Id      Issue_Id
2      10000394
2      10002451
2      10002453
2      10002454
3      10002871
3      10003080
3      10003176
3      10003311
6      10003052
6      10003055
6      10003117
Mike EghtebasDatabase and Application DeveloperCommented:
To test, I guess we need some repeat of say 10002451. Am I correct?

Insert #TableB(Category_Id,      Issue_Id) values
(2,     10000394)
,(2,     10002451)
,(3,     10002451)  --  I have added
,(4,     10002451)  --  I have added
,(2,      10002453)
,(2,      10002454)    
,(3,       10002871)
,(3,       10003080)
,(3,      10003176)    
,(3,       10003311)
,(6,       10003052)
,(6,      10003055)    
,(6,     10003117);

Here is temp tables:
create table #TableA(id int, Name varchar(100));
create table #TableB(Category_Id int, Issue_Id int);

Insert #TableA(Id,      Name) values
(1, 'SCRA')
, (2, 'Credit Bureau')
, (3, 'Fraud')                         
, (4, 'Identity Theft')
, (5, 'Attorney contact (including letter)')
, (6, 'Payment/Billing');

Insert #TableB(Category_Id,      Issue_Id) values
(2,     10000394)
,(2,     10002451)
,(3,     10002451)  --
,(4,     10002451)  --
,(2,      10002453)
,(2,      10002454)    
,(3,       10002871)
,(3,       10003080)
,(3,      10003176)    
,(3,       10003311)
,(6,       10003052)
,(6,      10003055)     
,(7,     10003117);
Select * from #TableA;
Select * from #TableB;

Open in new window


BTW, I couldn't make it work. I have invited a very experienced expert to take a look at this question.

Mike
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

flagshipcreditAuthor Commented:
Yes, that is correct... multiple Issue ID with different categories . I am trying to create one row for each unique issue ID and another column that combines the different categories it has .. so the final result set would look like the below

Issue ID            Issue Type
 10004516         Credit Bureau; Collections
 10004608         Dealer; Collections
Mike EghtebasDatabase and Application DeveloperCommented:
Finally, it seems I have figured this out:
Select Distinct Issue_Id , Issues
from (Select Issue_Id 
    ,Substring((Select ', ' + name
	  From #TableB inner join #TableA
	  On #TableB.Category_Id = #TableA.id
	  Where Issue_Id = t.Issue_Id
	  FOR XML PATH('')),2,99) As Issues
From #TableB t) dd (Issue_Id , Issues)
Where Issue_Id in (10002451,10002871,10003052)

Open in new window


Based on the data in the above temp tables it is producing:

10002451       Credit Bureau, Fraud, Identity Theft
10002871       Fraud
10003052       Payment/Billing

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
no points please:

I would use STUFF() instead of SUBSTRING(). STUFF() can remove the unwanted leading comma without having to specify the total length of the concatenated string.

I would also encourage the use of the APPLY operator, while the following may not look much different it is a bit more efficient:
select
      Issue_Id, max(CA.IssueTypes) AS IssueTypes
from TableB AS B
cross apply (
      SELECT
            STUFF((
                  SELECT
                        ', ' + A.name
                  FROM TableB
                  INNER JOIN TableA AS A ON TableB.Category_Id = A.id
                  WHERE B.Issue_Id = TableB.Issue_Id
                  FOR xml PATH ('')
            )
            ,1,1,'') 
  ) as CA (IssueTypes)
group by 
      Issue_Id
;

Open in new window

see: http://sqlfiddle.com/#!6/f7978/1 (suggest you look at the explain plans)

If you haven't already seen these I do recommend them:
Itzik Ben-Gan - Creative Uses of the APPLY Operator, session 1
Itzik Ben-Gan's - Creative Uses of the APPLY Operator, session 2
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

Thank you for the great solution. I tired hard to write cross apply solution. Now, I am learning it.

@flagshipcredit,

Please note WHERE Issue_Id in (10002451,10002871,10003052) I have added to Paul's solution in case you want to filter your output:
select
      Issue_Id, max(CA.IssueTypes) AS IssueTypes
from #TableB AS B
cross apply (
      SELECT
            STUFF((
                  SELECT
                        ', ' + A.name
                  FROM #TableB
                  INNER JOIN #TableA AS A ON #TableB.Category_Id = A.id
                  WHERE B.Issue_Id = #TableB.Issue_Id
                  FOR xml PATH ('')
            )
            ,1,1,'') 
  ) as CA (IssueTypes)
  WHERE Issue_Id in (10002451,10002871,10003052)
group by 
      Issue_Id
;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.