how to perform group by for the following scenario

Brian collins 141 Reputation points
2021-02-09T06:20:27.657+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-09T07:24:16.53+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-09T07:28:47.66+00:00

    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.