0

I have a scores table like this:

 code   | week | points

 1001   |  1   | 2
 1001   |  1   | 1
 1001   |  3   | 6
 2001   |  1   | 0
 2001   |  4   | 5
 2001   |  4   | 2

What I'd like is a result like this:

code     | 1  | 3 | 4

1001     | 3  | 6 | 
2001     | 0  |   | 7

I've written a simple group by which I could probably use to write some code around but I'd rather do the work in the SQL. http://sqlfiddle.com/#!15/8ff5d

select code, week, sum(points) as total
from scores
group by code, week
order by code, week;

And the result is:

code  | week  | total

1001  |  1    | 3
1001  |  3    | 6
2001  |  1    | 0
2001  |  4    | 7

I'm sure it's really simple but I'm stumped. Thanks in advance for any help.

aymericbeaumet
  • 6,853
  • 2
  • 37
  • 50
spannerj
  • 648
  • 1
  • 6
  • 11
  • Are you limited to just SQL? Perhaps you could reformat the data with a different language (perhaps PHP)? – Luke Jul 30 '15 at 08:14
  • Thanks for the help guys. That is what I wanted but had no idea what it was called. – spannerj Jul 30 '15 at 09:55

3 Answers3

0

You're looking for PIVOT Function:
similar question: Create a pivot table with PostgreSQL

When the number of columns is fix, then it's more or less simple. If the number is dynamic, then search for dynamic pivot.

Community
  • 1
  • 1
Thomas
  • 126
  • 1
  • 8
  • 1
    Equivalent in postgreSQL seems to be the crosstab() function, correct? – Luke Jul 30 '15 at 08:18
  • If a similar question with an accepted answer is already available on SO, you should flag the question as a duplicate. – Patrick Jul 30 '15 at 08:26
0

Generic method

select 
code,
sum(case when week=1 then points else 0 end) as week1,
sum(case when week=2 then points else 0 end) as week2,
sum(case when week=3 then points else 0 end) as week3,
sum(case when week=4 then points else 0 end) as week4
from table
group by code
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

What you're trying to do is actually not simple at all, I don't think! It looks like you want a set of results that has a variable number of columns, which is not really how SQL works, most of the time.

Think of it this way: if your table was much larger, and had many different weeks in it, your result would look something like this:

code     | 1  | 3 | 4 | 5 | 7 | 8 | 9 | ... | ...
1001     | 3  | 6 |   | 1 | 3 | etc. ...
2001     | 0  |   | 7 | 2 |   | ...

Some varieties of SQL let you pivot the table, which switches the orientation of rows and columns, which is basically what you want to do. However, I don't think PostgreSQL has that.

What you can do, though, is turn one set of results into an array, so you will get one column that has an array in it with an arbitrary number of values.

It looks like something like this might be what you want:

SELECT code, array_agg(ROW(week, total)) AS week_array
FROM (select code, week, sum(points) as total
  from scores
  group by code, week
  ORDER BY week) AS row
GROUP BY code
ORDER BY code;

The array will have tuples like {("1", 0), ("3", 8)} which means 0 points in week 1 and 8 points in week 3 for that code. This should work no matter how your table grows.

mrcheshire
  • 525
  • 2
  • 8
  • Yes, PostgreSQL has a pivot function and it is called `crosstab()` and you can find it in the `tablefunc` extension. There are already many questions and answers on SO about this. – Patrick Jul 30 '15 at 08:44
  • I stand corrected, crosstab() does look like postgres pivot! Sorry for including incorrect information in my answer. – mrcheshire Jul 30 '15 at 09:33
  • No problem! Cheerio for making the effort to put together a worked out answer. – Patrick Jul 30 '15 at 09:38
  • Thanks for that answer mrchesire. I like that as a simple solution and may well use it and do some additional work inside my application. – spannerj Jul 30 '15 at 10:05