Question 90517 in PostgreSQL Question posted in
The official documentation can be found here.

How to correctly pivot the table data in PostgreSQL using the 'case' statements/ cross tab function

I have a table comprising a list of artists added to a playlist during specific years (2018 – 2022). I want to pivot the data into five "year" columns with corresponding artist names added in those years. I used the ‘case’ statements. However, it shows one record per row and gives a null value for all other years.

Expected output:

Enter image description here

Using case statements gives me null values

So I tried the ‘crosstab’ function, but it still doesn’t output data as expected which is in different columns, corresponding to years without any blank fields with null values.

Enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    select max("2018") as "2018",max("2019") as "2019",max("2020") as "2020",max("2021")as "2021", max("2022") as "2022" from(
select
    YEAR_ADDED,
    row_number() over(partition by year_added order by artists_list) as rn,
    case when YEAR_ADDED = '2018' then artists_list   else null end as "2018",
    case when YEAR_ADDED = '2019' then artists_list else null end as "2019",
    case when YEAR_ADDED = '2020' then artists_list else null end as "2020",
    case when YEAR_ADDED = '2021' then artists_list   else null end as "2021",
    case when YEAR_ADDED = '2022' then artists_list else null end as "2022"
from year_added group by year_added,  artists_list) as temp
group by rn order by rn

    Gives output:

    enter image description here


  2. 0

    By piecing the screenshots together, it seems that you have a table with three columns: (year_added, track, artist) and you want the output looks like below:

    track                             |2018                               |2019                                          |2020                             |2021                               |
----------------------------------+-----------------------------------+----------------------------------------------+---------------------------------+-----------------------------------+
Alors on dance                    |Doug,Kieth,Roger                   |Alan,Chester,William                          |Nicholas                         |Maxwell                            |
Andalouse                         |Tyson                              |Daron,Domenic,Ramon                           |Barney,Chuck,Nicholas            |                                   |
Baila Morena                      |                                   |Hayden,Sebastian                              |                                 |                                   |
Bailando                          |Brad                               |Chester,Elijah,George,Julian                  |Barry,Roger                      |Doug,Jack,Kurt,Matt,Rick,Rocco     |
Beat it                           |Chris,Daniel                       |Denis                                         |Tom                              |Bryon,John,Mike                    |
Beggin                            |Anthony                            |Chad,Mike,Noah                                |Josh,Rufus                       |Denis                              |
Bette Davis Eyes                  |Abdul,Eduardo                      |Carter,Jacob                                  |Gil                              |Erick,Ron                          |

    If so, the query below may work for you:

    with cte as (
select track,
       case when year_added = 2018 then string_agg(artist,',') end as "2018",
       case when year_added = 2019 then string_agg(artist,',') end as "2019",
       case when year_added = 2020 then string_agg(artist,',') end as "2020",
       case when year_added = 2021 then string_agg(artist,',') end as "2021",
       case when year_added = 2022 then string_agg(artist,',') end as "2022"
  from tab_year_added
 group by track, year_added)
select track,
       max("2018") as "2018",
       max("2019") as "2019",
       max("2020") as "2020",
       max("2021") as "2021",
       max("2022") as "2022"
  from cte
 group by track
 order by track;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search