merge rows in sql server

Question

Select name, Subject, Score from Class

Tables

name |  Subject | Score
-----------------------
Ola     maths      20
Ola     pop        15
Ola     eng        74
Dodo    maths      21
Dodo     pop       19
Dodo     eng       54

Please any idea on how to make it display like this:

name |  Subject | Score
-----------------------
Ola     maths      20
        pop        15
        eng        74
Dodo    maths      21
         pop       19
         eng       54

Please any assistant will be appreciated

This type of transformation is usually done in the application layer. — Gordon Linoff, Apr 15 '20 at 14:44
Yup do it in the presentation layer ,sql isn't designed for this , it can be done but messy. — P.Salmon, Apr 15 '20 at 14:44
Agreed with the others, you do this at the report layer, but if you need to do it in T-SQL, if you want a _deterministic_ result, you need to provide some way to indicate to SQL Server why the order you're presenting makes sense. Why is `maths` first, `pop` second, etc.? A table is an unordered bag of rows so you need to define logic for sorting in order to determine which row is "first" - either by using a column or by using some kind of expression. — Aaron Bertrand, Apr 15 '20 at 14:51
@Fayemioladele Did any of the solutions below work for you? If you so can you accept an answer? — openshac, Apr 17 '20 at 11:25

score 0 · Answer 1 · answered Apr 15 '20 at 14:49

0

if you absolutely have to do this in SQL:

with cte as
(
select rn=row_number() over (partition by name order by 1)
    ,*
from [table]
)
select n = case when rn=1 then name else '' end,  Subject, Score
from cte
order by name,rn

But if you do this then you are losing the ability to use this data again.

answered Apr 15 '20 at 14:49

KeithL

5,348
3
19
25

While I don't know if it's important to the OP, this does not guarantee that `maths` will come first in each group. – Aaron Bertrand Apr 15 '20 at 14:52
1

Maybe include a case in the window statement to force order of subject. – P.Salmon Apr 15 '20 at 14:54
i left the order by as "1". that's where order can be made – KeithL Apr 15 '20 at 15:14
I didn't order it because there was no order asked for by OP – KeithL Apr 15 '20 at 15:17
Right, questions don't usually arrive in full, and teeth need to be pulled. Until this detail is clarified by the OP, I think it is reasonable to add to your answer that this happens to derive the result in the order they stated in the question, but this won't necessarily be the case always. And the reason I think this detail should be in the answer is because comments are far less visible (and also less permanent). – Aaron Bertrand Apr 15 '20 at 17:35
I want to thank all of you for your comments and ideas, i tried some of the hints, but still couldnt get what i wanted. But i found the following helpful https://stackoverflow.com/questions/13426616/what-is-the-easiest-way-to-make-a-banded-report-in-rdlc/13456096#13456096 – Fayemi oladele Apr 21 '20 at 10:51

score 0 · Answer 2 · answered Apr 15 '20 at 14:55

0

As previous people have already comment, this might not be your best approach but the following query returns your expected result:

create table Class (Name varchar(10), Subject varchar(10), Score int)

insert into Class
values ('Ola', 'maths', 20),
('Ola', 'pop', 15),
('Ola', 'eng', 15),
('Dodo', 'maths', 20),
('Dodo', 'pop', 15),
('Dodo', 'eng', 15)

select
    case when Row# = 1 then t.Name else null end,
    Subject,
    Score
from (
    select
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name ASC) AS Row#,
        Name,
        Subject,
        Score
    from Class) t

drop table Class

answered Apr 15 '20 at 14:55

openshac

4,966
5
46
77

This may be the right answer, but like the other answer, this doesn't guarantee that `maths` will come first, it just happens to look that way right now simply because of the order you inserted values. Insert in a different order, and/or add a covering index, and you will see the output change. – Aaron Bertrand Apr 15 '20 at 14:57
@AaronBertrand Completely agree with you, but without a definition of what the ordering rule it's difficult to provide more complete code. – openshac Apr 15 '20 at 15:16
Yep. As I said, this _may_ be right. I was just warning against implying that this guarantees to come out `maths`, `pop`, `eng` when in fact that is just a lucky coincidence. If the OP isn't expecting that, great! But in case they are, you should change the wording of "returns your expected result:" IMHO. – Aaron Bertrand Apr 15 '20 at 17:33
Thanks @AaronBertrand – openshac Apr 16 '20 at 19:26

merge rows in sql server

2 Answers2