0

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

Improve this question
1

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I was trying to pivot the table such that it shows all the artists added to a "playlist" corresponding to the year it was added in but unlike 2nd picture which shows only one result per row and shows null for rest of the years. I have sound the solution please refer the below answer. Thanks for your contribution.
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

Improve this answer

1 Comment

Please review Why not upload images of code/errors when asking a question? (e.g., "Images should only be used to illustrate problems that can't be made clear in any other way, such as to provide screenshots of a user interface.") and do the right thing (it covers program output as well). Thanks in advance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.