GROUP BY SQL QUERY SOME DATA ROWS AS HEADER

Question

Hi,

My data like below:

ID Name class score

---

1 P 1 20

2 P 1 20

3 A 2 10

3 A 2 20

Now I need data like below in Excel :
Name as group by
class range means like below

P
1
ID sum_score

---

1 20
2 20

A
2
ID sum_score

---

3 30

Could you please any help..,

Thanks

Answer 1

Hi @Madhu P ,

Welcome to the Microsoft TSQL Q&A Forum!

Please refer to:

    CREATE TABLE #test (ID int, Name char(15), class int, score int)  
    INSERT INTO #test VALUES(1,'P',1,20),(2,'P',1,20),(3,'A',2,10),(3,'A',2,20)  
      
    SELECT Name,class,ID, sum(score) sum_score  
    FROM #test  
    GROUP BY Name,class,ID  
    ORDER BY Name DESC  

Outout:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

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

Yes, good but I need like below
Name(which has group by) as Top row remain empty rows
class (which has group by) as Top row remain empty rows
above group related IDs records
No need to write Header , I need records as per image - please check image

Answer 3

The data in the same column needs to have the same data type. The name column is a string, which is different from the class column in data type and cannot be placed in the same column.

Does the following output meet your needs？

    SELECT DISTINCT Name,'' class,'' ID,'' sum_score FROM #test  
    UNION ALL  
    SELECT DISTINCT '', class,'' ,'' FROM #test  
    UNION ALL  
    SELECT Name,class,ID, sum(score) sum_score  
    FROM #test  
    GROUP BY Name,class,ID  
    ORDER BY Name DESC  

Output:

Or:

SELECT DISTINCT Name,'' class,'' ID,'' sum_score FROM #test  
UNION ALL  
SELECT DISTINCT Name,class,'' ID,'' sum_score FROM #test  
UNION ALL  
SELECT Name,class,ID, sum(score) sum_score  
FROM #test  
GROUP BY Name,class,ID  
ORDER BY Name DESC  

Output:

Echo