How to correctly pivot the table data in postgres using case statements/ cross tab function-CodePudding

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 5 "year" columns with corresponding artist names added in those years. I used the case statements however it shows one record per row and give a null value for all other years.

Expected output:

So I tried crosstab function but still doesnt output data as expected which is in different columns corresponding to years without any blank fields with null values.

CodePudding user response：

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;

However, if it's not the case, I would suggest you share more details (in text format) on

Table definition
Sample data
Expected outcome

CodePudding user response：

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: