1

I am new to Postgres and the function crosstab(). I have query like this:

select * from crosstab(
  'select artis_clean, release_year, total_count_song from top_artis_5year ' ,
   'select distinct release_year from top_artis_5year order by 1 '
    
) as (
 "artis_clean" text, "1965" int , "1966" int, "1967" int, "1968" int,"1969" int,"1970" int
);

I get a result like this:

artis_clean     1965    1966    1967    1968    1969    1970
------------    ----    ----    ----    ----    ----    -----
the beatles     null    null    23      null    null    null
led zepelin     null    null    null    null      18    null
the beatles       15    null    null    13        13    null
led zepelin       12    null    null    null    null    null
jimi hendrix    null    null    9       null    null    null
jimi hendrix       8    null    null    null    null    null

Why does artis_clean still have duplicate data, when I put artis_clean in the distinct?

 'select distinct release_year,artis_clean from top_artis_5year order by 1 '

The code won't work, Postgres says:

ERROR:  provided "categories" SQL must return 1 column of at least one row
SQL state: 42601
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dipretelin
  • 69
  • 1
  • 7

1 Answers1

4

The input needs to be sorted. Append ORDER BY 1 to the first argument of your first query.

Also, it makes no sense to query all release_year dynamically for the second argument while the column definition list is static anyway.

See:

To replace resulting NULL values, use COALESCE in the outer SELECT. Example:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228