how to perform group by for the following scenario

Question

Hello,

I have the data in the following temp table format and I'm having trouble pulling results that should look like the results in the screenshot. Please assist.

CREATE TABLE #temp(primaryGUID varchar(255)  
					, BookName varchar(255)  
					, citation varchar(50)  
					, indexID varchar(255)  
					, Titles VARCHAR(255))  
INSERT INTO #temp  
VALUES('1234-313-13145-94124','Book-1', '111.22','12345', 'Monitoring Data')  
, ('1234-313-13145-94124','Book-1', '111.23(a)','12355', 'Monitoring Systems')  
, ('1234-313-13145-94124','Book-1', '111.23(a)','12355', 'Monitoring Systems')  
, ('1234-313-13145-94124','Book-1', '111.23(a)','12355', 'Monitoring Systems')  
, ('1234-313-13145-94124','Book-1', '111.23(a)','112233', 'Monitoring Profiles')  
, ('1234-313-13145-94124','Book-1', '111.23(a)','112233', 'Monitoring Profiles')  
, ('1234-313-13145-94124','Book-1', '444.12','556677', 'Analyze Systems')  
, ('1234-313-13145-94124','Book-1', '444.12','556677', 'Analyze  Systems')  
  
, ('9999-313-13145-94124','Book-2', '777.66','998877', 'Support  Systems')  
, ('9999-313-13145-94124','Book-2', '777.66','998877', 'Support  Systems')  
, ('9999-313-13145-94124','Book-2', '777.66','5678', 'Refresh  Systems')  
, ('9999-313-13145-94124','Book-2', '888.99','5678', 'Refresh  Systems')  
, ('9999-313-13145-94124','Book-2', '888.99','5678', 'Refresh  Systems')  
  
  
  
SELECT *, count(citation) FROM #temp  
GROUP by primaryGUID, BookName, citation, indexID, Titles![65672-output.png][1]  
  
DROP TABLE #temp  

Thank you

Answer 1

Hi @Brian collins ,

Please refer below:

  ;with cte as(  
  SELECT primaryGUID, BookName, citation, replace(Titles,'  ',' ') Titles,count(citation)  count  
  FROM #temp  
   GROUP by primaryGUID, BookName, citation,replace(Titles,'  ',' ')  
  )   
  select  primaryGUID, BookName, citation  
  ,STRING_AGG(Titles+'('+cast(count as varchar(10)) +')',' ') Titles  
  ,count(Titles) Titles_count  
  from cte   
  group by primaryGUID, BookName, citation  

Output:

primaryGUID	BookName	citation	Titles	Titles_count  
1234-313-13145-94124	Book-1	111.22	Monitoring Data(1)	1  
1234-313-13145-94124	Book-1	111.23(a)	Monitoring Profiles(2) Monitoring Systems(3)	2  
1234-313-13145-94124	Book-1	444.12	Analyze Systems(2)	1  
9999-313-13145-94124	Book-2	777.66	Refresh Systems(1) Support Systems(2)	2  
9999-313-13145-94124	Book-2	888.99	Refresh Systems(2)	1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Brian collins ,

If your SQL Server version is 2016 and before, you could refer below:

;with cte as(  
  SELECT primaryGUID, BookName, citation, replace(Titles,'  ',' ') Titles,count(citation)  count  
  FROM #temp  
   GROUP by primaryGUID, BookName, citation,replace(Titles,'  ',' ')  
  )   
  select  primaryGUID, BookName, citation,  
  STUFF((SELECT ' '+Titles+'('+cast(count as varchar(10)) +')' FROM cte b  
				 where b.primaryGUID=b.primaryGUID and a.BookName=b.BookName and a.citation=b.citation  
				  FOR XML PATH ('')), 1, 1, '')  Titles  
  ,count(Titles) Titles_count  
  from cte a  
  group by primaryGUID, BookName, citation  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.