Combine multiple rows of data in SQL Server into a single row.

2 years ago
Benjamin Taylor
1 minute

To combine multiple rows of data into one row, you can achieve this by using the GROUP BY clause and aggregate functions.

Suppose we have the following data:

Table name: students

ID
Name
Grade

1
Alice
85

1
Alice
90

2
Bob
80

2
Bob
75

3
Charlie
95

3
Charlie
85

You can use the following query to consolidate the grades of each student into one row.

SELECT 
    id,
    name,
    GROUP_CONCAT(score ORDER BY score SEPARATOR ', ') AS scores
FROM 
    students
GROUP BY 
    id, name;

Outcome:

ID, Name, Grades

In the above query, we will use the GROUP_CONCAT function to concatenate the grades of each student into one string separated by commas. By using the ORDER BY clause, we can sort the grades in order.

bannerAds