CODEWITHSUNDEEP
sqlsql-server
Rahul Aggarwal
Rahul Aggarwal

Reputation: 291

SQL Query for showing data based on Name

I am creating a SQL query. My table as follows:-

DECLARE @Tab TABLE
(
    Name VARCHAR(10), 
    NUM INT,
    PracticeName VARCHAR(10)
)

INSERT INTO @Tab 
VALUES ('A', 25, 'Test'), ('B', 30, 'Test'), ('C', 236, 'Test1'),
       ('D', 217, 'Test'), ('E', 19, 'Test1')

The query I am using is :

 SELECT 
     PracticeName,
     'Total:' + CAST(SUM(NUM) as varchar(10)) + ' ('+   
                STUFF((SELECT ', ' + Name + ':' + CAST(NUM as varchar(10))  
                       FROM @Tab  
                       FOR XML PATH('')), 1, 1, '') +')'  
 FROM @Tab  
 GROUP BY PracticeName

The output I am getting is:

Test    Total:272 ( A:25, B:30, C:236, D:217, E:19)
Test1   Total:255 ( A:25, B:30, C:236, D:217, E:19)

I want the output as:-

Test      Total:272 ( A:25, B:30, D:217)
Test1     Total:255 (C:236, E:19)

Please help

Upvotes: 1

Views: 242

Answers (1)

beejm
beejm

Reputation: 2481

Just add a where clause on your stuff()

SELECT PracticeName,'Total:' + CAST(SUM(NUM) as varchar(10)) +' ('+   
 STUFF(  
  (  
  SELECT ', ' + s.Name +':'+ CAST(s.NUM as varchar(10))  
  FROM @Tab s 
  WHERE s.PracticeName = m.PracticeName
  FOR XML PATH('')  
  ), 1, 1, '') +')'  
 FROM @Tab m 
 GROUP BY PracticeName

Upvotes: 1

Related Questions