SQL Group By Multiple values on different columns

Question

I have a table like this:

Section    TestID        Score

Section1   1             50
Section2   1             32
Section3   1             22
Section1   2             22
Section2   2             17 
Section3   2             42

I'm looking to produce a table with each section and it's scores against all testIDs (up to a maximum of 3 scores). Is it possible to use a group by condition to produce a table similar to this:

Section    Score1     Score2

Section1   50          22
Section2   32          17
Section3   22          42

forpas · Accepted Answer · 2020-06-09 12:32:23Z

1

With ROW_NUMBER() window function and conditional aggregation:

select t.section,
  max(case when t.rn = 1 then t.score end) score1,
  max(case when t.rn = 2 then t.score end) score2
from (
  select *, row_number() over (partition by section order by testid) rn
  from tablename
) t
group by t.section

See the demo.
Results:

> section  | score1 | score2
> :------- | -----: | -----:
> Section1 |     50 |     22
> Section2 |     32 |     17
> Section3 |     22 |     42

edited Jun 9, 2020 at 12:32

answered Jun 9, 2020 at 11:59

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-06-09 11:55:42Z

0

You can use conditional aggregation:

select section,
       max(case when testid = 1 then score end) as score_1,
       max(case when testid = 2 then score end) as score_2
from t
group by section;

answered Jun 9, 2020 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Group By Multiple values on different columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related