0

I am looking for an efficient way to convert rows to columns in SQL. Can the name of the column depend on the line change? Here is my example:

Name table films and Visitors

Id | film
0  | comedy 
1  | horror 
2  | drama

Id | visitors
0  | 3
1  | 10
2  | 4
0  | 8
1  | 5
2  | 1

I must current sum of comedy, drama, horror where the column names reflects the category of films currently in use. I don't know how.. Could you write this? Please

Nemezis
  • 1
  • 1
  • 2
    Specify the expected result as well. And show us your current query attempt. – jarlh Aug 06 '19 at 10:50
  • 1
    The two answers make different things because it is not quite clear what you are expecting. Please clarify and add some expected output – S-Man Aug 06 '19 at 10:59

2 Answers2

0

I'm pretty sure you just want join and group by:

select f.film, sum(v.visitors)
from visitors v left join
     film f
     on v.id = f.id
group by f.film;

If this is what you want, this is a basic SQL query. JOIN is a fundamental concept and you should spend some time learning SQL. There are many books, tutorials, videos, and other resources available for this purpose.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Interesting, the different interpretations :) In my opinion the phrase "convert rows to columns" triggers the pivot. But maybe you are right and it is that basic. Perhaps the TO adds an expected output... – S-Man Aug 06 '19 at 10:58
0

For static 3 categories:

demo:db<>fiddle

SELECT
    SUM(visitors) FILTER (WHERE id = 0) AS comedy, 
    SUM(visitors) FILTER (WHERE id = 1) AS horror, 
    SUM(visitors) FILTER (WHERE id = 2) AS drama
FROM films
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • How do I change the script so that the name of the column will depend on the name of the movie category? When someone, for example, changes the comedies for a triller, then I would have to manually correct the name of the column. How could I not do this manually? – Nemezis Aug 06 '19 at 11:05
  • @Nemezis First please let us know if this is the result you are expecting in principle? – S-Man Aug 06 '19 at 11:06
  • Yes, I was looking for it exactly. Thanks! – Nemezis Aug 06 '19 at 11:31
  • @Nemezis Unfortunately this is not that simple in Postgres. But you may have a look at this question where the problem is being discussed: https://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns – S-Man Aug 06 '19 at 11:42