Share via

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.
13,367 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,191 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
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,191 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.